﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Globalization;

namespace Lapas.Common
{
    /// <summary>
    /// Last update 20070414.HN.Sat
    /// </summary>
    public class OExcelDocument
    {
        private XmlDocument excelDocument;

        public XmlDocument ExcelDocument
        {
            get { return excelDocument; }
            set { excelDocument = value; }
        }

        public OExcelDocument(XmlDocument _excelDocument)
        {
            excelDocument = _excelDocument;
        }

        public XmlNode SelectNode(XmlDocument doc, string NodeName)
        {
            XmlNode resultNode = null;
            foreach (XmlNode node in doc.ChildNodes)
            {
                if (node.Name == NodeName)
                {
                    resultNode = node;
                    break;
                }
            }
            return resultNode;
        }

        public XmlNode SelectNode(XmlNode xmlNode, string NodeName)
        {
            XmlNode resultNode = null;
            foreach (XmlNode node in xmlNode.ChildNodes)
            {
                if (node.Name.ToUpper() == NodeName.ToUpper())
                {
                    resultNode = node;
                    break;
                }
            }
            return resultNode;
        }

        public XmlNode FindTemplateRow(XmlNode tableObjectNode)
        {
            XmlNode TemplateNode = null;
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "999999"))
                                {
                                    return rowNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        #region DSSoHuuCKLuuKy_HaNoi
        public List<XmlNode> FindTemplateRows_DSSoHuuCKLuuKy_HaNoi(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling);
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        // 20070713.Fri.HN TriDM : stockName -> StockNameViet
        // 20070906 TriDM: sua ngay chot va ngay tao bao cao
        public static void Generate_DSSoHuuCKLuuKy_HaNoi_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            double total = 0;

            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
            tableObject.InnerXml = tableObject.InnerXml.Replace("888888888888", dataSource.ParValue);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", dataSource.DateOwnerConfirm.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Purpose#", System.Web.HttpUtility.HtmlEncode(dataSource.Purpose));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Day#", dataSource.TransactionDate.ToString("dd"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.TransactionDate.ToString("MM"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.TransactionDate.ToString("yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", AppSettings.Company_Code);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", AppSettings.Report_CompanyAddress_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_Location#", AppSettings.Report_Location);
            #endregion


            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data
               
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //XmlNode templateRow = template.FindTemplateRow(TableObject);
                //XmlNode newRow = templateRow.Clone();
                //templateRow.ParentNode.InsertAfter(newRow, templateRow);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuCKLuuKy_HaNoi(tableObject);
                XmlNode templateRow1 = templateRows[0];
                XmlNode templateRow2 = templateRows[1];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ho ten
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Name"]));

                            // So DKNSH
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyNumber"]);

                            // Loai DKSH
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyType"]);

                            // Loai hinh
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["OwnerType"]);

                            // Quoc tich
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Nationality"]);

                            // Dia chi
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Address"]));

                            // Loai tai khoan
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerOrInternal"]);

                            #endregion

                            #region Bind data to Row 2
                            XmlNode newRow2 = templateRow2.Clone();
                            // Loai chung khoan
                            newRow2.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SecuritiesType"]);

                            // So luong
                            int soLuong = Convert.ToInt32(dataSource.Data.Rows[i]["Volume"]);
                            newRow2.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Volume"]);
                            total = total + soLuong;

                            // Ghi chu
                            #endregion

                            #region Add row
                            //newRow1.InnerXml = newRow1.InnerXml.Replace("999999", string.Format("{0}", i*111111));
                            tableObject.InsertAfter(newRow2, templateRow2);
                            tableObject.InsertAfter(newRow1, templateRow2);
                            #endregion
                        }

                    // Bind first record data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ho ten
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Name"]));

                    // So DKNSH
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["IdentifyNumber"]);

                    // Loai DKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["IdentifyType"]);

                    // Loai hinh
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["OwnerType"]);

                    // Quoc tich
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Nationality"]);

                    // Dia chi
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Address"]));

                    // Loai tai khoan
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerOrInternal"]);

                    #endregion

                    #region Bind data to Row 2
                    // Loai chung khoan
                    templateRow2.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SecuritiesType"]);

                    // So luong
                    templateRow2.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Volume"]);
                    int soLuong0 = Convert.ToInt32(dataSource.Data.Rows[0]["Volume"]);
                    total = total + soLuong0;

                    // Ghi chu
                    #endregion

                    #endregion

                    tableObject.InnerXml = tableObject.InnerXml.Replace("999999999999", total.ToString());
                }
                catch { }

                #endregion
            }
            else
            {
                #region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuCKLuuKy_HaNoi(tableObject);
                XmlNode templateRow1 = templateRows[0];
                XmlNode templateRow2 = templateRows[1];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ho ten
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So DKNSH
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "0";

                    // Loai DKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "0";

                    // Loai hinh
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "0";

                    // Quoc tich
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Loai tai khoan
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "0";

                    #endregion

                    #region Bind data to Row 2
                    // Loai chung khoan
                    templateRow2.ChildNodes[8].ChildNodes[0].InnerXml = "0";

                    // So luong
                    templateRow2.ChildNodes[9].ChildNodes[0].InnerXml = "0";

                    // Ghi chu
                    #endregion

                    #endregion

                    tableObject.InnerXml = tableObject.InnerXml.Replace("999999999999", "0");
                }
                catch { }

                #endregion
            }



            #region Write result
            //System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(path, System.Text.Encoding.UTF8);
            //writer.Formatting = System.Xml.Formatting.Indented;
            //template.ExcelDocument.Save(writer);
            //writer.Close();
            template.ExcelDocument.Save(path);
            #endregion
        }


        #endregion

        #region DSSoHuuCKLuuKy_HCM
        public List<XmlNode> FindTemplateRows(XmlNode tableObjectNode, string caption, int nextSib)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            XmlNode tmpNode;
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                tmpNode = rowNode;
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == caption))
                                {
                                    for (int i = 0; i < nextSib; i++)
                                        tmpNode = rowNode.NextSibling;
                                        TemplateNode.Add(tmpNode);
                                        return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        /// <summary>
        /// 20081209 Haids:
        /// Danh sách sở hữu chứng khoán, Danh sách chốt cuối cùng
        /// </summary>
        public List<XmlNode> FindTemplateRows_DSSoHuuChungKhoan(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        /// <summary>
        /// Thucnt:
        /// Báo cáo thanh toán cổ tức bằng tiền mặt
        /// </summary>
        public List<XmlNode> FindTemplateRows_PaymentInMoney(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }


        /// <summary>
        /// Thucnt:
        /// Báo cáo tổng hợp tình hình hoạt động môi giới
        /// </summary>
        public List<XmlNode> FindTemplateRows_CustomerOpenClose(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "Nội dung"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        /// <summary>
        /// 20080820 Haids:
        /// update for Giấy đăng ký mua chứng khoán
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        public List<XmlNode> FindTemplateRows_GiayDangKyMuaCK(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        /// <summary>
        /// HN 20100527 Giapbd:
        /// Báo cáo tổng hợp chuyển nhượng quyền mua chứng khoán
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        public List<XmlNode> FindTemplateRows_TongHopCNQuyenMuaCK(XmlNode tableObjectNode, string template)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
                foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
                {
                    if (rowNode.Name == "Row")
                    {
                        foreach (XmlNode cellNode in rowNode.ChildNodes)
                        {
                            if (cellNode.Name == "Cell")
                            {
                                foreach (XmlNode dataNode in cellNode.ChildNodes)
                                {
                                    if ((dataNode.Name == "Data") && (dataNode.InnerText == template))
                                    {

                                        TemplateNode.Add(rowNode);
                                        return TemplateNode;
                                    }
                                }
                            }
                        }
                    }

            }
            return TemplateNode;
        }

          /// <summary>
        /// HN 20100527 Giapbd:
        /// Báo cáo tổng hợp chuyển nhượng quyền mua chứng khoán
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        public XmlNode FillData2Row(XmlNode xmlRow, string rowId, string rowHeader, string customerNameViet, string ownerRegisterNo, string addressViet, 
            string country, string customerID, string allowByQuantity, string transferQuantity, string receiveQuantity, string remainQuantity, string description)
        {
            xmlRow.ChildNodes[2].ChildNodes[0].InnerXml = rowHeader;
            xmlRow.ChildNodes[3].ChildNodes[0].InnerXml = rowId;
            // Họ tên
            xmlRow.ChildNodes[4].ChildNodes[0].InnerXml = customerNameViet;
            // Số ĐKNSH
            xmlRow.ChildNodes[5].ChildNodes[0].InnerXml = ownerRegisterNo;
            // Địa chỉ
            xmlRow.ChildNodes[6].ChildNodes[0].InnerXml = addressViet;
            //Quốc tịch
            xmlRow.ChildNodes[7].ChildNodes[0].InnerXml = country;
            //Số TKLK
            xmlRow.ChildNodes[8].ChildNodes[0].InnerXml = customerID;
            // Số lượng quyền mua được phân bổ ( trong nước)
            if (allowByQuantity!="")
                xmlRow.ChildNodes[9].ChildNodes[0].InnerXml = allowByQuantity;
            else
                xmlRow.ChildNodes[9].ChildNodes[0].InnerXml = "0";
            //Số lượng quyên mua CN ( trong nước)
            if ( transferQuantity !="")
            xmlRow.ChildNodes[10].ChildNodes[0].InnerXml =transferQuantity;
            else
                xmlRow.ChildNodes[10].ChildNodes[0].InnerXml = "0";
            //Số lượng quyền mua nhân CH (trong nước)
            if (receiveQuantity!="")
            xmlRow.ChildNodes[11].ChildNodes[0].InnerXml = receiveQuantity;
            else
                xmlRow.ChildNodes[11].ChildNodes[0].InnerXml = "0";
            //Số lượng quyền mua hiện nắm giữ (trong nước)
            if(remainQuantity!="")
            xmlRow.ChildNodes[12].ChildNodes[0].InnerXml = remainQuantity;
            else
                xmlRow.ChildNodes[12].ChildNodes[0].InnerXml = "0";
            //Ghi chú
            xmlRow.ChildNodes[13].ChildNodes[0].InnerXml = description;
            return xmlRow;
        }
       
        // 20070713.Fri.HN TriDM : stockName -> StockNameViet
        // 20070906 TriDM: sua ngay chot va ngay tao bao cao
        // 20080225 Haids: update BoardType
        public static void Generate_DSSoHuuCKLuuKy_HCM_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            double total = 0;

            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            string sendTo;
            if (dataSource.BoardType == Common.BoardType.M.ToString())
            {
                sendTo = AppSettings.VSDBranch;
            }
            else
            {
                sendTo = AppSettings.VSDName;
            }
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", sendTo);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
            tableObject.InnerXml = tableObject.InnerXml.Replace("888888888888", dataSource.ParValue);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", dataSource.DateOwnerConfirm.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Purpose#", System.Web.HttpUtility.HtmlEncode(dataSource.Purpose));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#Day#", dataSource.TransactionDate.ToString("dd"));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.TransactionDate.ToString("MM"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Day#", ".....");
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", ".....");
            
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.TransactionDate.ToString("yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", AppSettings.DepositoryCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("Trụ sở chính: {0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_Location#", AppSettings.Report_Location);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportFooter3#", AppSettings.FooterReport3);
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data
               
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //XmlNode templateRow = template.FindTemplateRow(TableObject);
                //XmlNode newRow = templateRow.Clone();
                //templateRow.ParentNode.InsertAfter(newRow, templateRow);
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];                
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ho ten
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Name"]));

                            // So DKNSH
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyNumber"]);

                            // Loai DKSH
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyType"]);

                            // Loai hinh
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["OwnerType"]);

                            // Quoc tich
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Nationality"]));

                            // Dia chi
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Address"]));

                            // Loai tai khoan
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerOrInternal"]);

                            #endregion

                            #region Bind data to Row 2 - > move to row 1
                            
                            // Loai chung khoan
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SecuritiesType"]);

                            // So luong
                            int soLuong = Convert.ToInt32(dataSource.Data.Rows[i]["Volume"]);
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Volume"]);
                            total = total + soLuong;

                            // Ghi chu
                            #endregion

                            #region Add row
                            //newRow1.InnerXml = newRow1.InnerXml.Replace("999999", string.Format("{0}", i*111111));
                            //tableObject.InsertAfter(newRow2, templateRow2);
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ho ten
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Name"]));

                    // So DKNSH
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["IdentifyNumber"]);

                    // Loai DKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["IdentifyType"]);

                    // Loai hinh
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["OwnerType"]);

                    // Quoc tich
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Nationality"]));

                    // Dia chi
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Address"]));

                    // Loai tai khoan
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerOrInternal"]);

                    #endregion

                    #region Bind data to Row 2 - > Move to row 1
                    // Loai chung khoan
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SecuritiesType"]);

                    // So luong
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Volume"]);
                    int soLuong0 = Convert.ToInt32(dataSource.Data.Rows[0]["Volume"]);
                    total = total + soLuong0;

                    // Ghi chu
                    #endregion

                    #endregion

                    tableObject.InnerXml = tableObject.InnerXml.Replace("999999999999", total.ToString());
                }
                catch
                { }

                #endregion
            }
            else
            {
                #region Data
                
                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];                
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ho ten
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So DKNSH
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "0";

                    // Loai DKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "0";

                    // Loai hinh
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "0";

                    // Quoc tich
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Loai tai khoan
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "0";

                    #endregion

                    #region Bind data to Row 2 - > Move to Row 1
                    // Loai chung khoan
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "0";

                    // So luong
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "0";

                    // Ghi chu
                    #endregion

                    #endregion

                    tableObject.InnerXml = tableObject.InnerXml.Replace("999999999999", "0");
                }
                catch { }

                #endregion
            }

            #region Write result
            //System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(path, System.Text.Encoding.UTF8);
            //writer.Formatting = System.Xml.Formatting.Indented;
            //template.ExcelDocument.Save(writer);
            //writer.Close();
            template.ExcelDocument.Save(path);
            #endregion
        }


        public static void Generate_ReportTMTC_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            string sendTo;
            if (dataSource.BoardType == Common.BoardType.M.ToString())
            {
                sendTo = AppSettings.VSDBranch;
            }
            else
            {
                sendTo = AppSettings.VSDName;
            }
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", sendTo);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
            tableObject.InnerXml = tableObject.InnerXml.Replace("888888888888", dataSource.ParValue);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", dataSource.DateOwnerConfirm.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Purpose#", System.Web.HttpUtility.HtmlEncode(dataSource.Purpose));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#Day#", dataSource.TransactionDate.ToString("dd"));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.TransactionDate.ToString("MM"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Day#", ".....");
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", ".....");

            tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.TransactionDate.ToString("yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", AppSettings.DepositoryCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("Trụ sở chính: {0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_Location#", AppSettings.Report_Location);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportFooter3#", AppSettings.FooterReport3);
            #endregion



            #region Write result
        
            template.ExcelDocument.Save(path);
            #endregion
        }


        /// <summary>
        /// Tuanha 2009-12-22
        /// phiếu đối chiếu tài khoản khách hàng
        /// </summary>
        public List<XmlNode> FindTemplateRows_CustomerCompare(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        #endregion

        /// <summary>
        /// 20080707 Haids:
        /// Danh sách khách hàng mở tài khoản
        /// </summary>
        
        #region "Danh sách khách hàng mở tài khoản"
        public static void Generate_DSKhachHangMoTaiKhoan_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, bool openAccount)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            string LoaiKH = "CN";
                            switch (Convert.ToString(dataSource.Data.Rows[i]["CustomerType"]))
                            {
                                case "IF": LoaiKH = "CN"; break;
                                case "ED": LoaiKH = "TC"; break;
                                case "EF": LoaiKH = "TC"; break;
                                default: LoaiKH = "CN"; break;
                            }

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ten khach hang
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // So Tai khoan
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerID"]);

                            // So CMT/ Ho chieu
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Dia chi
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));

                            // Ngay cap
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardDate"]);

                            // Noi cap
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CardIssuer"]));

                            // Loai hop dong
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = LoaiKH; //Convert.ToString(dataSource.Data.Rows[i]["CustomerType"]);

                            // Ngay mo tai khoan
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["OpenDate"]);

                            if (openAccount)
                            {
                                // Quoc tich
                                newRow1.ChildNodes[9].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Country"]));
                            }
                            else
                            {
                                // Ngay dong tai khoan
                                newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CloseDate"]);
                                // Quoc tich
                                newRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Country"]));
                            }

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    string LoaiKHfirst = "CN";
                    switch (Convert.ToString(dataSource.Data.Rows[0]["CustomerType"]))
                    {
                        case "IF": LoaiKHfirst = "CN"; break;
                        case "ED": LoaiKHfirst = "TC"; break;
                        case "EF": LoaiKHfirst = "TC"; break;
                        default: LoaiKHfirst = "CN"; break;
                    }
                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerID"]);

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Dia chi
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // Ngay cap
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardDate"]);

                    // Noi cap
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CardIssuer"]));

                    // Loai hop dong
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = LoaiKHfirst; //Convert.ToString(dataSource.Data.Rows[0]["CustomerType"]);

                    // Ngay mo tai khoan
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["OpenDate"]);

                    if (openAccount)
                    {
                        // Quoc tich
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Country"]));
                    }
                    else
                    {
                        // Ngay dong tai khoan
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CloseDate"]);
                        // Quoc tich
                        templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Country"]));
                    }

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Ngay cap
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Noi cap
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Loai hop dong
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Ngay mo tai khoan
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    if (openAccount)
                    {
                        // Quoc tich
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";
                    }
                    else
                    {
                        // Ngay dong tai khoan
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";
                        // Quoc tich
                        templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = "";
                    }
                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion

        }
        #endregion

        /// <summary>
        /// 20090720 Haids:
        /// Báo cáo phí giao dịch theo khách hàng
        /// </summary>
        public static void Generate_BaoCaoPhiMoiGioiTheoKhachHang_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, bool openAccount)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", dataSource.BranchCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TradeCode#", dataSource.TradeCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerId#", dataSource.CustomerID);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerName#", dataSource.CustomerNameViet);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            string LoaiLenh = "Bán";
                            if (Convert.ToString(dataSource.Data.Rows[i]["OrderSide"]) == OrderSide.B.ToString())
                            {
                                LoaiLenh = "Mua";
                            }
                            else
                            {
                                LoaiLenh = "Bán";
                            }

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ngày Giao dịch
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["OrderDate"]);

                            // Loại lệnh
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = LoaiLenh;

                            // Mã chứng khoán
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["StockCode"].ToString();

                            // Khối lượng
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedVolume"]);

                            // Giá khớp
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedPrice"]);

                            // Giá trị giao dịch
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedValue"]);

                            // Tỷ lệ phí
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[i]["FeeRate"].ToString().Substring(0, 4));

                            // Phí giao dịch
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["FeeValue"]) != 0 ? Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[i]["FeeValue"]) / 100000) : "0";
                           

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    string LoaiLenhFirst = "Bán";
                    if (Convert.ToString(dataSource.Data.Rows[0]["OrderSide"]) == OrderSide.B.ToString())
                    {
                        LoaiLenhFirst = "Mua";
                    }
                    else
                    {
                        LoaiLenhFirst = "Bán";
                    }

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngày giao dịch
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["OrderDate"]);

                    // Loại lệnh
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = LoaiLenhFirst;

                    // Mã chứng khoán
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["StockCode"].ToString();

                    // Khối lượng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedVolume"]);

                    // Giá khớp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedPrice"]);

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedValue"]);

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[0]["FeeRate"].ToString().Substring(0, 4));

                    // Phí giao dịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[0]["FeeValue"]) != 0 ? Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[0]["FeeValue"]) / 100000) : "0";

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngày giao dịch
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "01/01/2009";

                    // Loại lệnh
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "Bán";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "FPT";

                    // Khối lượng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "1000";

                    // Giá khớp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "65";

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "65000000";

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "0";

                    // Phí giao dịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "0";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        /// <summary>
        /// 20090720 Haids:
        /// Báo cáo tổng hợp phí giao dịch loại 1
        /// </summary>
        public static void Generate_BaoCaoPhiMoiGioiTongHopLoai1_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, bool openAccount)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", dataSource.BranchCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TradeCode#", dataSource.TradeCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BoardType#", dataSource.BoardType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerType#", dataSource.CustomerType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            string LoaiLenh = "Bán";
                            if (Convert.ToString(dataSource.Data.Rows[i]["OrderSide"]) == OrderSide.B.ToString())
                            {
                                LoaiLenh = "Mua";
                            }
                            else
                            {
                                LoaiLenh = "Bán";
                            }

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Số tài khoản
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);

                            // Loại lệnh
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = LoaiLenh;

                            // Mã chứng khoán
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["StockCode"].ToString();

                            // Khối lượng
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedVolume"]);

                            // Giá khớp
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedPrice"]);

                            // Giá trị giao dịch
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedValue"]);

                            // Tỷ lệ phí
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[i]["FeeRate"].ToString().Substring(0, 4));

                            // Phí giao dịch
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[i]["FeeValue"])).ToString();


                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    string LoaiLenhFirst = "Bán";
                    if (Convert.ToString(dataSource.Data.Rows[0]["OrderSide"]) == OrderSide.B.ToString())
                    {
                        LoaiLenhFirst = "Mua";
                    }
                    else
                    {
                        LoaiLenhFirst = "Bán";
                    }

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số tài khoản
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);

                    // Loại lệnh
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = LoaiLenhFirst;

                    // Mã chứng khoán
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["StockCode"].ToString();

                    // Khối lượng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedVolume"]);

                    // Giá khớp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedPrice"]);

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedValue"]);

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[0]["FeeRate"].ToString().Substring(0, 4));

                    // Phí giao dịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[0]["FeeValue"])).ToString();
                    

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số tài khoản
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "002C101268";

                    // Loại lệnh
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "Bán";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "FPT";

                    // Khối lượng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "1000";

                    // Giá khớp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "65";

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "65000000";

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "0";

                    // Phí giao dịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "0";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }


        /// <summary>
        /// 20090729 Haids:
        /// Xuất excel Danh sách đăng ký thông tin tài khoản nhà đầu tư
        /// </summary>
        public static void Generate_DanhSachDangKyThongTinTaiKhoanNDT_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyDepository#", AppSettings.DepositoryCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", AppSettings.VSDName);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#VSDName#", AppSettings.VSDName);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ngày chốt
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["LastDate"].ToString();

                            // Họ và tên
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // Loại ĐKSH
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["CardType"].ToString();

                            // Số ĐKSH
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]));

                            // Ngày cấp
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["CardDate"].ToString();

                            // Nơi cấp
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CardIssuer"]));

                            // Loại hình cổ đông
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = dataSource.Data.Rows[i]["CustomerType"].ToString();

                            // Quốc tịch
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CountryNo"]);
                            // Địa chỉ
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));
                            // Điện thoại
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Mobile"]));
                            // Email
                            newRow1.ChildNodes[11].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Email"]));
                            // Số tài khoản giao dịch
                            newRow1.ChildNodes[12].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);
                            // Mã chứng khoán
                            newRow1.ChildNodes[13].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["StockCode"]));
                            // Chứng khoán giao dịch
                            newRow1.ChildNodes[14].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKGiaoDich"]);
                            // Chứng khoán hạn chế
                            newRow1.ChildNodes[15].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKHanChe"]);
                            // Chứng khoán chờ thanh toán
                            newRow1.ChildNodes[16].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKCTT"]);
                            // Chứng khoán cầm cố
                            newRow1.ChildNodes[17].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKCamCo"]);
                            // Chứng khoán chờ giao dịch
                            newRow1.ChildNodes[18].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKCGD"]);
                            // Chứng khoán chơ giao dịch hạn chế
                            newRow1.ChildNodes[19].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKCGDHanChe"]);
                            // Ghi chú
                            newRow1.ChildNodes[20].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["GhiChu"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngày chốt
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["LastDate"].ToString();

                    // Họ và tên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // Loại ĐKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["CardType"].ToString();

                    // Số ĐKSH
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]));

                    // Ngày cấp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["CardDate"].ToString();

                    // Nơi cấp
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CardIssuer"]));

                    // Loại hình cổ đông
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = dataSource.Data.Rows[0]["CustomerType"].ToString();

                    // Quốc tịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CountryNo"]);
                    // Địa chỉ
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));
                    // Điện thoại
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Mobile"]));
                    // Email
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Email"]));
                    // Số tài khoản giao dịch
                    templateRow1.ChildNodes[12].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);
                    // Mã chứng khoán
                    templateRow1.ChildNodes[13].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["StockCode"]));
                    // Chứng khoán giao dịch
                    templateRow1.ChildNodes[14].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKGiaoDich"]);
                    // Chứng khoán hạn chế
                    templateRow1.ChildNodes[15].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKHanChe"]);
                    // Chứng khoán chờ thanh toán
                    templateRow1.ChildNodes[16].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKCTT"]);
                    // Chứng khoán cầm cố
                    templateRow1.ChildNodes[17].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKCamCo"]);
                    // Chứng khoán chờ giao dịch
                    templateRow1.ChildNodes[18].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKCGD"]);
                    // Chứng khoán chờ giao dịch hạn chế
                    templateRow1.ChildNodes[19].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKCGDHanChe"]);
                    // Ghi chú
                    templateRow1.ChildNodes[20].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["GhiChu"]);

                    #endregion
                }
                catch (Exception ex)
                {
                    DisplayMessage.ShowError(ex.Message);
                }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";
                    // Ngày chốt
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";
                    // Họ và tên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";
                    // Loại ĐKSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";
                    // Số ĐKSH
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";
                    // Ngày cấp
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";
                    // Nơi cấp
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";
                    // Loại hình cổ đông
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";
                    // Quốc tịch
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";
                    // Địa chỉ
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";
                    // Điện thoại
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = "";
                    // Email
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = "";
                    // Số tài khoản giao dịch
                    templateRow1.ChildNodes[12].ChildNodes[0].InnerXml = "";
                    // Mã chứng khoán
                    templateRow1.ChildNodes[13].ChildNodes[0].InnerXml = "";
                    // Chứng khoán giao dịch
                    templateRow1.ChildNodes[14].ChildNodes[0].InnerXml = "";
                    // Chứng khoán hạn chế
                    templateRow1.ChildNodes[15].ChildNodes[0].InnerXml = "";
                    // Chứng khoán chờ thanh toán
                    templateRow1.ChildNodes[16].ChildNodes[0].InnerXml = "";
                    // Chứng khoán cầm cố
                    templateRow1.ChildNodes[17].ChildNodes[0].InnerXml = "";
                    // Chứng khoán chờ giao dịch
                    templateRow1.ChildNodes[18].ChildNodes[0].InnerXml = "";
                    // Chứng khoán chờ giao dịch hạn chế
                    templateRow1.ChildNodes[19].ChildNodes[0].InnerXml = "";
                    // Ghi chú
                    templateRow1.ChildNodes[20].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        /// <summary>
        /// 20090731 Haids:
        /// Báo cáo số lượng tài khoản gửi UBCKNN
        /// </summary>
        public static void Generate_SoLuongTaiKhoanGuiUBCKNN_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, DataTable T1, DataTable T2)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", dataSource.BranchCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);

            //Số lượng tài khoản
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a11#", dataSource.Data.Rows[0]["CanhanTNDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a12#", dataSource.Data.Rows[0]["TochucTNDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a13#", dataSource.Data.Rows[0]["CanhanNNDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a14#", dataSource.Data.Rows[0]["TochucNNDK"].ToString());

            tableObject.InnerXml = tableObject.InnerXml.Replace("#a21#", dataSource.Data.Rows[0]["CanhanTNOpen"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a22#", dataSource.Data.Rows[0]["TochucTNOpen"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a23#", dataSource.Data.Rows[0]["CanhanNNOpen"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a24#", dataSource.Data.Rows[0]["TochucNNOpen"].ToString());

            tableObject.InnerXml = tableObject.InnerXml.Replace("#a31#", dataSource.Data.Rows[0]["CanhanTNClose"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a32#", dataSource.Data.Rows[0]["TochucTNClose"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a33#", dataSource.Data.Rows[0]["CanhanNNClose"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a34#", dataSource.Data.Rows[0]["TochucNNClose"].ToString());

            double a41 = Convert.ToDouble(dataSource.Data.Rows[0]["CanhanTNDK"]) + (Convert.ToDouble(dataSource.Data.Rows[0]["CanhanTNOpen"]) - Convert.ToDouble(dataSource.Data.Rows[0]["CanhanTNClose"]));
            double a42 = Convert.ToDouble(dataSource.Data.Rows[0]["TochucTNDK"]) + (Convert.ToDouble(dataSource.Data.Rows[0]["TochucTNOpen"]) - Convert.ToDouble(dataSource.Data.Rows[0]["TochucTNClose"]));
            double a43 = Convert.ToDouble(dataSource.Data.Rows[0]["CanhanNNDK"]) + (Convert.ToDouble(dataSource.Data.Rows[0]["CanhanNNOpen"]) - Convert.ToDouble(dataSource.Data.Rows[0]["CanhanNNClose"]));
            double a44 = Convert.ToDouble(dataSource.Data.Rows[0]["TochucNNDK"]) + (Convert.ToDouble(dataSource.Data.Rows[0]["TochucNNOpen"]) - Convert.ToDouble(dataSource.Data.Rows[0]["TochucNNClose"]));

            tableObject.InnerXml = tableObject.InnerXml.Replace("#a41#", a41.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a42#", a42.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a43#", a43.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a44#", a44.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#a45#", Convert.ToString(a41 + a42 + a43 + a44));

            //Cổ phiếu và Chứng chỉ quỹ
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b11#", T1.Rows[0]["MatchedVolumeBuyDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b12#", T1.Rows[0]["MatchedVolumeSellDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b13#", T1.Rows[0]["MatchedVolumeBuy"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b14#", T1.Rows[0]["MatchedVolumeSell"].ToString());

            tableObject.InnerXml = tableObject.InnerXml.Replace("#b21#", T1.Rows[0]["MatchedValueBuyDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b22#", T1.Rows[0]["MatchedValueSellDK"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b23#", T1.Rows[0]["MatchedValueBuy"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b24#", T1.Rows[0]["MatchedValueSell"].ToString());

            double b15 = Convert.ToDouble(T1.Rows[0]["MatchedVolumeBuyDK"]) + Convert.ToDouble(T1.Rows[0]["MatchedVolumeBuy"]);
            double b16 = Convert.ToDouble(T1.Rows[0]["MatchedVolumeSellDK"]) + Convert.ToDouble(T1.Rows[0]["MatchedVolumeSell"]);

            tableObject.InnerXml = tableObject.InnerXml.Replace("#b15#", b15.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b16#", b16.ToString());

            double b25 = Convert.ToDouble(T1.Rows[0]["MatchedValueBuyDK"]) + Convert.ToDouble(T1.Rows[0]["MatchedValueBuy"]);
            double b26 = Convert.ToDouble(T1.Rows[0]["MatchedValueSellDK"]) + Convert.ToDouble(T1.Rows[0]["MatchedValueSell"]);

            tableObject.InnerXml = tableObject.InnerXml.Replace("#b25#", b25.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#b26#", b26.ToString());

            //Trái phiếu
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c11#", T2.Rows[0]["MatchedVolumeBuyDK_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c12#", T2.Rows[0]["MatchedVolumeSellDK_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c13#", T2.Rows[0]["MatchedVolumeBuy_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c14#", T2.Rows[0]["MatchedVolumeSell_D"].ToString());

            tableObject.InnerXml = tableObject.InnerXml.Replace("#c21#", T2.Rows[0]["MatchedValueBuyDK_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c22#", T2.Rows[0]["MatchedValueSellDK_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c23#", T2.Rows[0]["MatchedValueBuy_D"].ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c24#", T2.Rows[0]["MatchedValueSell_D"].ToString());

            double c15 = Convert.ToDouble(T2.Rows[0]["MatchedVolumeBuyDK_D"]) + Convert.ToDouble(T2.Rows[0]["MatchedVolumeBuy_D"]);
            double c16 = Convert.ToDouble(T2.Rows[0]["MatchedVolumeSellDK_D"]) + Convert.ToDouble(T2.Rows[0]["MatchedVolumeSell_D"]);

            tableObject.InnerXml = tableObject.InnerXml.Replace("#c15#", c15.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c16#", c16.ToString());

            double c25 = Convert.ToDouble(T2.Rows[0]["MatchedValueBuyDK_D"]) + Convert.ToDouble(T2.Rows[0]["MatchedValueBuy_D"]);
            double c26 = Convert.ToDouble(T2.Rows[0]["MatchedValueSellDK_D"]) + Convert.ToDouble(T2.Rows[0]["MatchedValueSell_D"]);

            tableObject.InnerXml = tableObject.InnerXml.Replace("#c25#", c25.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#c26#", c26.ToString());


            #endregion

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion

        }

        /// <summary>
        /// 20090720 Haids:
        /// Báo cáo tổng hợp phí giao dịch loại 2
        /// </summary>
        public static void Generate_BaoCaoPhiMoiGioiTongHopLoai2_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, bool openAccount)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", dataSource.BranchCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TradeCode#", dataSource.TradeCodeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BoardType#", dataSource.BoardType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerType#", dataSource.CustomerType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Số tài khoản
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);

                            // Tên khách hàng
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerName"]));

                            // Giá trị giao dịch
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MatchedValue"]);

                            // Tỷ lệ phí
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[i]["FeeRate"].ToString().Substring(0, 4));

                            // Phí giao dịch
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[i]["FeeValue"])).ToString();


                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số tài khoản
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);

                    // Tên khách hàng
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerName"]));

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MatchedValue"]);

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = string.Format("{0}", dataSource.Data.Rows[0]["FeeRate"].ToString().Substring(0, 4));

                    // Phí giao dịch
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[0]["FeeValue"])).ToString();

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số tài khoản
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "002C101268";

                    // Tên khách hàng
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "Đỗ Sơn Hải";

                    // Giá trị giao dịch
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "65000000";

                    // Tỷ lệ phí
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "0";

                    // Phí giao dịch
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "0";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        /// <summary>
        /// 20080709 Haids
        /// Danh sách khách hàng ủy quyền
        /// </summary>

        #region "Danh sách khách hàng ủy quyền"
        public static void Generate_DSKhachHangUyQuyen_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Tên người ủy quyền
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // Số tài khoản
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerID"]);

                            // So CMT/ Ho chieu
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Dia chi
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));

                            // Ngay ủy quyền
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DateProxy"]);

                            // Tên người nhận ủy quyền
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["ProxyNameViet"]));

                            // Số CMT/Hộ chiếu
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["ProxyCardIdentity"]); 

                            // Địa chỉ
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["ProxyAddressViet"]));

                            // Phạm vi ủy quyền
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["ProxyType"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Tên người ủy quyền
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // Số tài khoản
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerID"]);

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Dia chi
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // Ngày ủy quyền
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DateProxy"]);

                    // Tên người nhận ủy quyền
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["ProxyNameViet"]));

                    // Số CMT/Hộ chiếu
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["ProxyCardIdentity"]);

                    // Địa chỉ
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["ProxyAddressViet"]));
                    
                    // Phạm vi ủy quyền
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["ProxyType"]);

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten người ủy quyền
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Ngày ủy quyền
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Tên người nhận ủy quyền
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Số CMT/Hộ chiếu
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Địa chỉ
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    // Phạm vi ủy quyền
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion

        }
        #endregion

        /// <summary>
        /// 20080708 Haids
        /// Báo cáo tình hình đóng mở tài khoản lưu ký
        /// </summary>
        #region "Bao cao tinh hinh dong mo tai khoan luu ky"
        public static void Generate_BaoCaoTinhHinhDongMoTKLK_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("Tên TVLK: {0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DepositoryCode#", string.Format("Số ĐKTVLK: {0}", AppSettings.DepositoryCode));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.FromDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ToDate#", dataSource.ToDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Kinhgui#", string.Format("Kính gửi: {0}", AppSettings.VSDName));
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Họ tên nhà đầu tư
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // Đóng/Mở tài khoản
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AccountStatus"]);

                            // Loại hình
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerType"]);

                            // Quốc tịch
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Country"]));

                            // Số ĐKSH
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Loại ĐKSH
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentityType"]);

                            // Ngày cấp
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardDate"]);

                            // Nơi cấp
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CardIssuer"]));

                            // Số TK lưu ký
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerID"]);

                            // Địa chỉ liên hệ
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));

                            // Ghi chú
                            newRow1.ChildNodes[11].ChildNodes[0].InnerXml = string.Empty; //System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Notes"]));

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Họ tên nhà đầu tư
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // Đóng/mở tài khoản
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AccountStatus"]);

                    // Loại hình
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerType"]);

                    // Quốc tịch
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Country"]));

                    // Số ĐKSH
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Loại ĐKSH
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentityType"]);

                    // Ngày cấp
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardDate"]);

                    // Nơi cấp
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CardIssuer"]));

                    // Số TK lưu ký
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerID"]);

                    // Địa chỉ liên hệ
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // Ghi chú
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = string.Empty; //System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Notes"]));

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Họ tên nhà đầu tư
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // Đóng/mở tài khoản
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // Loại hình
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Quốc tịch
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Số ĐKSH
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Loại ĐKSH
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Ngày cấp
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Nơi cấp
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    // Số TK lưu ký
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";

                    // Địa chỉ liên hệ
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = "";

                    // Ghi chú
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion

        /// <summary>
        /// 20080819 Haids:
        /// Danh sách nhà đầu tư đăng ký đặt mua chứng khoán
        /// GiapBD:Update 22/07/2010
        /// (Đăng ký phát hành thêm)
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        #region "Danh sách nhà đầu tư đăng ký đặt mua chứng khoán"

        public static void Generate_DSNhaDauTuDangKyDatMuaCK_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, bool isBranch)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");
            
            string stockName = string.Empty;
            //decimal sumQuantity = 0;
            //decimal sumAmount = 0;

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}{1}", AppSettings.Report_CompanyName_vi, dataSource.TradeCodeText));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_AddressViet#", AppSettings.Report_CompanyAddress_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DepositoryCode#", string.Format("{0}", AppSettings.DepositoryCode));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            if (dataSource.StockName.Contains("&"))
            {
                stockName = dataSource.StockName.Replace('&', '-');
            }
            else
            {
                stockName = dataSource.StockName.ToString();
            }
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", stockName);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ParValue#", dataSource.ParValue.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#RightExecRate#", dataSource.RightExecRate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#LastRegisterDate#", Convert.ToDateTime(dataSource.DateOwnerConfirm).ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockPrice#", Convert.ToDecimal(dataSource.StockPrice).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TimeRegisterDate#","Từ " + dataSource.OpenDate + " đến " + dataSource.CloseDate);
            if (isBranch)
            {//Nếu in theo chi nhánh
                tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", string.Format("Hội sở {0}", AppSettings.Report_CompanyName_vi));
            }
            else
            {
                tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", dataSource.SendTo);
            }
            

            //for (int i = 0; i <= dataSource.Data.Rows.Count - 1; i++)
            //{
            //    sumQuantity += Convert.ToDecimal(dataSource.Data.Rows[i]["RegisteredQuantity"]);
            //    sumAmount += Convert.ToDecimal(dataSource.Data.Rows[i]["Amount"]);
            //}
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#SumQuantity#", sumQuantity.ToString("N0"));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#SumAmount#", sumAmount.ToString("C0"));

            #endregion
            int tableObjectColumn = 0;

            #region Count col
            foreach (XmlNode colNode in tableObject.ChildNodes)
            {
                if (colNode.Name == "Column") { tableObjectColumn++; }
                if (colNode.Name == "Row") { break; }
            }
            #endregion

            #region Find template rows
            //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
            List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);

            XmlNode templateRow1 = templateRows[0];
            XmlNode newRow1 = templateRow1.Clone();
            int count = 0;
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                
                try
                {
                    #region Fill data
                    for (int i = dataSource.Data.Rows.Count - 1; i >= 0; i--)
                    {
                        try
                        {
                            #region Bind data to Row 1
                            count++;
                            if (i == 0)
                                newRow1 = templateRow1;
                            else
                                newRow1 = templateRow1.Clone();
                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);
                            // Mã CK
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = dataSource.StockCode;
                            // TVLK
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = string.Format("{0}", AppSettings.DepositoryCode);
                            // Họ tên nhà đầu tư
                            // DisplayMessage.Show(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]), MessageType.Notice);
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString((dataSource.Data.Rows[i]["CustomerNameViet"] == null) ? string.Empty : dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // Số ĐKSH
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString((dataSource.Data.Rows[i]["CardIdentity"] == null) ? string.Empty : dataSource.Data.Rows[i]["CardIdentity"]));
                            // Ngày cấp
                            //DisplayMessage.Show(Convert.ToString(dataSource.Data.Rows[i]["CardDate"]), MessageType.Notice);
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString((dataSource.Data.Rows[i]["CardDate"] == null) ? string.Empty : dataSource.Data.Rows[i]["CardDate"]));//.Split(new char[] { ' ' })[0];
                            // Địa chỉ
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString((dataSource.Data.Rows[i]["AddressViet"] == null) ? string.Empty : dataSource.Data.Rows[i]["AddressViet"]));
                            // Tài khoản giao dịch
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString((dataSource.Data.Rows[i]["CustomerId"] == null) ? string.Empty : dataSource.Data.Rows[i]["CustomerId"]);

                            // Loại cổ phiếu
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString((dataSource.Data.Rows[i]["StockType"] == null) ? string.Empty : dataSource.Data.Rows[i]["StockType"]);

                            // Số lượng đặt mua
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToDecimal((dataSource.Data.Rows[i]["RegisteredQuantity"] == null) ? 0 : dataSource.Data.Rows[i]["RegisteredQuantity"]).ToString();

                            // Số tiền phải trả
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = Convert.ToString(Convert.ToDecimal((dataSource.Data.Rows[i]["Amount"] == null) ? 0 : dataSource.Data.Rows[i]["Amount"]) / 100000);

                            // Ghi chú
                            newRow1.ChildNodes[11].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString((dataSource.Data.Rows[i]["Description"] == null) ? string.Empty : dataSource.Data.Rows[i]["Description"]));

                            #endregion

                            #region Add row
                            if(i!=0)
                                tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }
                        catch (Exception ex) { DisplayMessage.Show(Convert.ToString(ex), MessageType.Error); };
                    }

                }
                catch (Exception e)
                {
                    DisplayMessage.Show(Convert.ToString(e), MessageType.Error);
                }

                    #endregion
            }
            if (count == 0)
                tableObject.RemoveChild(templateRow1);
           
                       

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion

        /// <summary>
        /// 20080820 Haids:
        /// Giấy đăng ký mua chứng khoán (Đăng ký phát hành thêm)
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        #region "Giấy đăng ký mua chứng khoán"

        public static void Generate_GiayDangKyMuaChungKhoan_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");
            string stockName = string.Empty;

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", string.Format("{0}", AppSettings.Report_CompanyTel));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DepositoryCode#", string.Format("{0}", AppSettings.DepositoryCode));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BankAccountNumber#", dataSource.Company);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Fax#", string.Format("{0}",AppSettings.Fax));
            if (dataSource.StockName.Contains("&"))
            {
                stockName = dataSource.StockName.Replace('&', '-');
            }
            else
            {
                stockName = dataSource.StockName.ToString();
            }
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockNameViet#", stockName);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ParValue#", Convert.ToDecimal(dataSource.ParValue).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", Convert.ToDateTime(dataSource.DateOwnerConfirm).ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockPrice#", Convert.ToDecimal(dataSource.StockPrice).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", dataSource.SendTo);
            decimal sumAllowQuanity = 0;
            sumAllowQuanity = Convert.ToDecimal(dataSource.Data.Rows[0]["AllowBuyQuantity_C"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["AllowBuyQuantity_F"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["AllowBuyQuantity_P"]);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SumAllowQuantity#", sumAllowQuanity.ToString("#,##0"));
            decimal sumRegisteredQuantity = 0;
            sumRegisteredQuantity = Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_C"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_F"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_P"]);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SumRegisteredQuantity#", sumRegisteredQuantity.ToString("#,##0"));
            UInt64 sumValue = 0;
            sumValue = (Convert.ToUInt64(dataSource.Data.Rows[0]["RegisteredQuantity_C"]) + Convert.ToUInt64(dataSource.Data.Rows[0]["RegisteredQuantity_F"]) + Convert.ToUInt64(dataSource.Data.Rows[0]["RegisteredQuantity_P"])) * Convert.ToUInt64(dataSource.StockPrice);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SumValue#", sumValue.ToString("C0"));

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_GiayDangKyMuaCK(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Số lượng quyền mua trong nước
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity_C"]);

                            // Số lượng quyền mua nước ngoài
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity_F"]);

                            // Số lượng quyền mua tự doanh
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity_P"]);

                            // Số lượng đặt mua trong nước
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RegisteredQuantity_C"]);

                            // Số lượng đặt mua nước ngoài
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RegisteredQuantity_F"]);

                            // Số lượng đặt mua tự doanh
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RegisteredQuantity_P"]);

                            // Số tiền đặt mua trong nước
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[i]["RegisteredQuantity_C"]) * dataSource.StockPrice) / 100000);

                            // Số tiền đặt mua nước ngoài
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[i]["RegisteredQuantity_F"]) * dataSource.StockPrice) / 100000);

                            // Số tiền đặt mua tự doanh
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[i]["RegisteredQuantity_P"]) * dataSource.StockPrice) / 100000);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số lượng quyền mua trong nước
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AllowBuyQuantity_C"]);

                    // Số lượng quyền mua nước ngoài
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AllowBuyQuantity_F"]);

                    // Số lượng quyền mua tự doanh
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AllowBuyQuantity_P"]);

                    // Số lượng đặt mua trong nước
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RegisteredQuantity_C"]);

                    // Số lượng đặt mua nước ngoài
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RegisteredQuantity_F"]);

                    // Số lượng đặt mua tự doanh
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RegisteredQuantity_P"]);

                    // Số tiền đặt mua trong nước
                    if (Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_C"]) == 0)
                    {
                        templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToDecimal("0").ToString();
                    }
                    else
                    {
                        templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_C"]) * dataSource.StockPrice) / 100000);
                    }

                    // Số tiền đặt mua nước ngoài
                    if (Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_F"]) == 0)
                    {
                        templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToDecimal("0").ToString();
                    }
                    else
                    {
                        templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_F"]) * dataSource.StockPrice) / 100000);
                    }

                    // Số tiền đặt mua tự doanh
                    if (Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_P"]) == 0)
                    {
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToDecimal("0").ToString();
                    }
                    else
                    {
                        templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString((Convert.ToDecimal(dataSource.Data.Rows[0]["RegisteredQuantity_P"]) * dataSource.StockPrice) / 100000);
                    }

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_GiayDangKyMuaCK(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Số lượng quyền mua trong nước
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // Số lượng quyền mua nước ngoài
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // Số lượng quyền mua tự doanh
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Số lượng đặt mua trong nước
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Số lượng đặt mua nước ngoài
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Số lượng đặt mua tự doanh
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Số tiền đặt mua trong nước
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Số tiền đặt mua nước ngoài
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    // Số tiền đặt mua tự doanh
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion

        /// <summary>
        /// HN 20100527 giapbd:
        /// Báo cáo tổng hợp chuyển nhượng quyền mua ck
        /// </summary>
        /// <param name="tableObjectNode"></param>
        /// <returns></returns>
        #region "DS tổng hợp chuyển nhượng quyền mua CK"

        public static void Generate_DSTongHopCNQuyenMuaCk_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");
            string stockName = string.Empty;

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", string.Format("{0}", AppSettings.Report_CompanyTel));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DepositoryCode#", string.Format("{0}", AppSettings.DepositoryCode));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerId#", dataSource.Company.ToString()); //AppSettings.Company_PrivateAccount);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Fax#", string.Format("{0}", AppSettings.Fax));
            if (dataSource.StockName.Contains("&"))
            {
                stockName = dataSource.StockName.Replace('&', '-');
            }
            else
            {
                stockName = dataSource.StockName.ToString();
            }
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockNameViet#", stockName);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", Convert.ToDateTime(dataSource.DateOwnerConfirm).ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", dataSource.SendTo);
          
            #endregion
             decimal sumAllowByQuantityTNCN =0, sumTransferQuantityTNCN=0,sumReceiveQuantityTNCN = 0, sumRemainQuantityTNCN=0,
                        sumAllowByQuantityTNTC =0, sumTransferQuantityTNTC=0,sumReceiveQuantityTNTC = 0, sumRemainQuantityTNTC=0,
                        sumAllowByQuantityNNCN =0, sumTransferQuantityNNCN=0,sumReceiveQuantityNNCN = 0, sumRemainQuantityNNCN=0,
                        sumAllowByQuantityNNTC =0, sumTransferQuantityNNTC=0,sumReceiveQuantityNNTC = 0, sumRemainQuantityNNTC=0,
                        sumAllowByQuantityTD =0, sumTransferQuantityTD=0,sumReceiveQuantityTD = 0, sumRemainQuantityTD=0,
                        sumAllowByQuantity = 0, sumTransferQuantity = 0, sumReceiveQuantity = 0, sumRemainQuantity = 0;
              int rowIdTNCN = 0, rowIdTNTC =0, rowIdNNCN =0, rowIdNNTC = 0, rowIdTD=0 ;
              bool isTNCN = false, isTNTC = false, isNNCN = false, isNNTC = false, isTD = false;
              
            #region Find template rows for Domestic Private Customer
              //Môi giới, trong nước , cá nhân
              XmlNode templateRowTNCN = template.FindTemplateRows_TongHopCNQuyenMuaCK(tableObject, "TNCN")[0];
              //Môi giới, trong nước, tổ chức
              XmlNode templateRowTNTC = template.FindTemplateRows_TongHopCNQuyenMuaCK(tableObject, "TNTC")[0];
              //Môi giới, nước ngoài, cá nhân
              XmlNode templateRowNNCN = template.FindTemplateRows_TongHopCNQuyenMuaCK(tableObject, "NNCN")[0];
              //Môi giới, nước ngoài, tổ chức
              XmlNode templateRowNNTC = template.FindTemplateRows_TongHopCNQuyenMuaCK(tableObject, "NNTC")[0];
              //Tự doanh
              XmlNode templateRowTD = template.FindTemplateRows_TongHopCNQuyenMuaCK(tableObject, "Tự doanh")[0];

              XmlNode newRow1=templateRowTNCN;
              string rowHeader = "";
              #endregion
            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i >= 0; i--)
                        {
                            //Khách hàng Môi giới / Trong nước / cá nhân
                            if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Trong nước" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Cá nhân")
                            {
                                isTNCN = true;
                                rowIdTNCN ++;
                            }
                            //Khách hàng Môi giới / Trong nước / tổ chức
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Trong nước" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Tổ chức")
                            {
                                isTNTC = true;
                                rowIdTNTC ++;
                            }
                            //Khách hàng Môi giới / Nước ngoài / cá nhân
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Nước ngoài" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Cá nhân")
                            {
                                isNNCN = true;
                              rowIdNNCN ++;
                            }
                            //Khách hàng Môi giới / Nước ngoài / tổ chức
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Nước ngoài" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Tổ chức")
                            {
                                isNNTC = true;
                               rowIdNNTC++;
                            }
                            //Khách hàng Tự doanh
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Tự doanh")
                            {
                                isTD = true;
                    
                            }
                        }
                        for (int i = dataSource.Data.Rows.Count - 1; i >= 0; i--)
                        {
                            //Khách hàng Môi giới / Trong nước / cá nhân
                            if(Convert.ToString(dataSource.Data.Rows[i]["DORP"])=="Môi giới" && 
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"])=="Trong nước" && 
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"])=="Cá nhân")
                            {
                                #region Bind data to Row 1
                                if (rowIdTNCN == 1)
                                {
                                    rowHeader = "Cá nhân";
                                    newRow1 = templateRowTNCN;
                                }
                                else
                                {
                                    newRow1 = templateRowTNCN.Clone();
                                    rowHeader = "";
                                }
                                newRow1 = template.FillData2Row(newRow1, Convert.ToString(rowIdTNCN--),rowHeader,
                                    Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["OwnerRegisterNo"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["Country"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["TransferQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["ReceiveQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["RemainQuantity"]),
                                     "");
                                // Số lượng quyền mua được phân bổ ( trong nước)
                                sumAllowByQuantityTNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["AllowBuyQuantity"]);
                                //Số lượng quyên mua CN ( trong nước)
                                if (!  DBNull.Value.Equals( dataSource.Data.Rows[i]["TransferQuantity"]))
                               sumTransferQuantityTNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["TransferQuantity"]);
                                //Số lượng quyền mua nhân CH (trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["ReceiveQuantity"]))
                               sumReceiveQuantityTNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["ReceiveQuantity"]);
                                //Số lượng quyền mua hiện nắm giữ (trong nước)
                               sumRemainQuantityTNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["RemainQuantity"]);
                                #endregion

                                #region Add row
                               if (rowIdTNCN != 1)
                                    tableObject.InsertAfter(newRow1, templateRowTNCN);
                                #endregion
                            }
                            //Khách hàng Môi giới / Trong nước / tổ chức
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Trong nước" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Tổ chức")
                            {
                                #region Bind data to Row 1
                                if (rowIdTNTC == 1)
                                {
                                    rowHeader = "Tổ chức";
                                    newRow1 = templateRowTNTC;
                                }
                                else
                                {
                                    newRow1 = templateRowTNTC.Clone();
                                    rowHeader = "";
                                }
                                newRow1 = template.FillData2Row(newRow1, Convert.ToString(rowIdTNTC--),rowHeader,
                                    Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["OwnerRegisterNo"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["Country"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["TransferQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["ReceiveQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["RemainQuantity"]),
                                     "");
                                // Số lượng quyền mua được phân bổ ( trong nước)
                                sumAllowByQuantityTNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["AllowBuyQuantity"]);
                                //Số lượng quyên mua CN ( trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["TransferQuantity"]))
                                sumTransferQuantityTNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["TransferQuantity"]);
                                //Số lượng quyền mua nhân CH (trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["ReceiveQuantity"]))
                                sumReceiveQuantityTNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["ReceiveQuantity"]);
                                //Số lượng quyền mua hiện nắm giữ (trong nước)
                                sumRemainQuantityTNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["RemainQuantity"]);
                                #endregion

                                #region Add row
                                if (rowIdTNTC != 1)
                                    tableObject.InsertAfter(newRow1, templateRowTNTC);
                                #endregion
                            }
                            //Khách hàng Môi giới / Nước ngoài / cá nhân
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Nước ngoài" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Cá nhân")
                            {
                                #region Bind data to Row 1
                                if (rowIdNNCN == 1)
                                {
                                    rowHeader = "Cá nhân";
                                    newRow1 = templateRowNNCN;
                                }
                                else
                                {
                                    rowHeader = "";
                                    newRow1 = templateRowNNCN.Clone();
                                }
                                newRow1 = template.FillData2Row(newRow1, Convert.ToString(rowIdNNCN--), rowHeader,
                                    Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["OwnerRegisterNo"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["Country"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["TransferQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["ReceiveQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["RemainQuantity"]),
                                     "");
                                // Số lượng quyền mua được phân bổ ( trong nước)
                                sumAllowByQuantityNNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["AllowBuyQuantity"]);
                                //Số lượng quyên mua CN ( trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["TransferQuantity"]))
                                sumTransferQuantityNNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["TransferQuantity"]);
                                //Số lượng quyền mua nhân CH (trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["ReceiveQuantity"]))
                                sumReceiveQuantityNNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["ReceiveQuantity"]);
                                //Số lượng quyền mua hiện nắm giữ (trong nước)
                                sumRemainQuantityNNCN += Convert.ToDecimal(dataSource.Data.Rows[i]["RemainQuantity"]);
                                #endregion

                                #region Add row
                                if (rowIdNNCN != 1)
                                    tableObject.InsertAfter(newRow1, templateRowNNCN);
                                #endregion
                            }
                            //Khách hàng Môi giới / Nước ngoài / tổ chức
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Môi giới" &&
                                Convert.ToString(dataSource.Data.Rows[i]["DomesticForeignViet"]) == "Nước ngoài" &&
                                Convert.ToString(dataSource.Data.Rows[i]["CustomerTypeViet"]) == "Tổ chức")
                            {
                                #region Bind data to Row 1
                                if (rowIdNNTC == 1)
                                {
                                    rowHeader = "Tổ chức";
                                    newRow1 = templateRowNNTC;
                                }
                                else
                                {
                                    rowHeader = "";
                                    newRow1 = templateRowNNTC.Clone();
                                }
                                newRow1 = template.FillData2Row(newRow1, Convert.ToString(rowIdNNTC--),rowHeader,
                                    Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["OwnerRegisterNo"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["Country"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["TransferQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["ReceiveQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["RemainQuantity"]),
                                     "");
                                // Số lượng quyền mua được phân bổ ( trong nước)
                                sumAllowByQuantityNNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["AllowBuyQuantity"]);
                                //Số lượng quyên mua CN ( trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["TransferQuantity"]))
                                sumTransferQuantityNNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["TransferQuantity"]);
                                //Số lượng quyền mua nhân CH (trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["ReceiveQuantity"]))
                                sumReceiveQuantityNNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["ReceiveQuantity"]);
                                //Số lượng quyền mua hiện nắm giữ (trong nước)
                                sumRemainQuantityNNTC += Convert.ToDecimal(dataSource.Data.Rows[i]["RemainQuantity"]);
                                #endregion

                                #region Add row
                                if (rowIdNNTC != 1)
                                    tableObject.InsertAfter(newRow1, templateRowNNTC);
                                #endregion
                            }
                            //Khách hàng Tự doanh
                            else if (Convert.ToString(dataSource.Data.Rows[i]["DORP"]) == "Tự doanh")
                            {
                                #region Bind data to Row 1
                               //++rowIdTD;
                                templateRowTD = template.FillData2Row(templateRowTD, "","",
                                    Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["OwnerRegisterNo"]),
                                    Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["Country"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["AllowBuyQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["TransferQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["ReceiveQuantity"]),
                                     Convert.ToString(dataSource.Data.Rows[i]["RemainQuantity"]),
                                     "");
                                // Số lượng quyền mua được phân bổ ( trong nước)
                                sumAllowByQuantityTD += Convert.ToDecimal(dataSource.Data.Rows[i]["AllowBuyQuantity"]);
                                //Số lượng quyên mua CN ( trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["TransferQuantity"]))
                                sumTransferQuantityTD += Convert.ToDecimal(dataSource.Data.Rows[i]["TransferQuantity"]);
                                //Số lượng quyền mua nhân CH (trong nước)
                                if (!DBNull.Value.Equals(dataSource.Data.Rows[i]["ReceiveQuantity"]))
                                sumReceiveQuantityTD += Convert.ToDecimal(dataSource.Data.Rows[i]["ReceiveQuantity"]);
                                //Số lượng quyền mua hiện nắm giữ (trong nước)
                                sumRemainQuantityTD += Convert.ToDecimal(dataSource.Data.Rows[i]["RemainQuantity"]);
                                #endregion

                            }
                        }

                #endregion
            }
            //fill template row if it didn't fill
            if (!isTNCN )
                templateRowTNCN = template.FillData2Row(templateRowTNCN, "","Cá nhân", "", "", "", "", "", "", "", "", "", "");
            if (!isTNTC)
                templateRowTNTC = template.FillData2Row(templateRowTNTC, "","Tổ chức", "", "", "", "", "", "", "", "", "", "");
            if (!isNNCN)
                templateRowNNCN = template.FillData2Row(templateRowNNCN, "","Cá nhân", "", "", "", "", "", "", "", "", "", "");
            if (!isNNTC)
                templateRowNNTC = template.FillData2Row(templateRowNNTC, "","Tổ chức", "", "", "", "", "", "", "", "", "", "");
            if (!isTD )
                templateRowTD = template.FillData2Row(templateRowTD, "","", "", "", "", "", "", "", "", "", "", "");
            //Count sum
            // Số lượng quyền mua được phân bổ ( trong nước)
            sumAllowByQuantity = sumAllowByQuantityTNCN + sumAllowByQuantityTNTC + sumAllowByQuantityNNCN + sumAllowByQuantityNNTC + sumAllowByQuantityTD;
            //Số lượng quyên mua CN ( trong nước)
            sumTransferQuantity = sumTransferQuantityTNCN + sumTransferQuantityTNTC + sumTransferQuantityNNCN + sumTransferQuantityNNTC + sumTransferQuantityTD;
            //Số lượng quyền mua nhân CH (trong nước)
            sumReceiveQuantity = sumReceiveQuantityTNCN + sumReceiveQuantityTNTC + sumReceiveQuantityNNCN + sumReceiveQuantityNNTC + sumReceiveQuantityTD;
            //Số lượng quyền mua hiện nắm giữ (trong nước)
            sumRemainQuantity = sumRemainQuantityTNCN + sumRemainQuantityTNTC + sumRemainQuantityNNCN + sumRemainQuantityNNTC + sumRemainQuantityTD;
          
            //Fill sum
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumAllowByQuantityTNCN#", Convert.ToString(sumAllowByQuantityTNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumTransferQuantityTNCN#", Convert.ToString(sumTransferQuantityTNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumReceiveQuantityTNCN#", Convert.ToString(sumReceiveQuantityTNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumRemainQuantityTNCN#", Convert.ToString(sumRemainQuantityTNCN));

            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumAllowByQuantityTNTC#", Convert.ToString(sumAllowByQuantityTNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumTransferQuantityTNTC#", Convert.ToString(sumTransferQuantityTNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumReceiveQuantityTNTC#", Convert.ToString(sumReceiveQuantityTNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumRemainQuantityTNTC#", Convert.ToString(sumRemainQuantityTNTC));

            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumAllowByQuantityNNCN#", Convert.ToString(sumAllowByQuantityNNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumTransferQuantityNNCN#", Convert.ToString(sumTransferQuantityNNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumReceiveQuantityNNCN#", Convert.ToString(sumReceiveQuantityNNCN));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumRemainQuantityNNCN#", Convert.ToString(sumRemainQuantityNNCN));

            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumAllowByQuantityNNTC#", Convert.ToString(sumAllowByQuantityNNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumTransferQuantityNNTC#", Convert.ToString(sumTransferQuantityNNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumReceiveQuantityNNTC#", Convert.ToString(sumReceiveQuantityNNTC));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumRemainQuantityNNTC#", Convert.ToString(sumRemainQuantityNNTC));

            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumAllowByQuantity#", Convert.ToString(sumAllowByQuantity));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumTransferQuantity#", Convert.ToString(sumTransferQuantity));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumReceiveQuantity#", Convert.ToString(sumReceiveQuantity));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#sumRemainQuantity#", Convert.ToString(sumRemainQuantity));


            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion

        /// <summary>
        /// 20091016 Haids:
        /// Danh sách bán chứng khoán lô lẻ
        /// </summary>
        /// <param name="dataSource"></param>
        /// <param name="template"></param>
        /// <param name="path"></param>
        #region "Danh sách bán chứng khoán lô lẻ"
        public static void Generate_DanhSachBanChungKhoanLoLe_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_AddressViet#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyTel#", string.Format("{0}", AppSettings.Report_CompanyTel));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyDepository#", string.Format("{0}", AppSettings.DepositoryCode));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BoardName#", dataSource.BoardType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Footer3#", dataSource.Description);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SumQuantity#", Convert.ToDecimal(dataSource.TKLKCaNhan).ToString("N0", new CultureInfo("en-us")));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SumAmount#", Convert.ToDecimal(dataSource.TKLKToChuc).ToString("N0", new CultureInfo("en-us")));
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ngày giao dịch
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToDateTime(dataSource.Data.Rows[i]["TransactionDate"]).ToString("dd/MM/yyyy");

                            // Họ và tên
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]);

                            // Số ĐKNSH
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Ngày cấp số ĐKNSH
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToDateTime(dataSource.Data.Rows[i]["CardDate"]).ToString("dd/MM/yyyy");

                            // Số tài khoản
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);

                            // Mã chứng khoán
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);

                            // Khối lượng
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Quantity"]);

                            // Giá tham chiếu
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[i]["BasicPrice"])).ToString();

                            // Tỷ lệ tính Cstr({OddStockSheet.Price}/{OddStockSheet.BasicPrice}*100)+"%"
                            //Math.Round(Convert.ToDecimal(dataSource.Data.Rows[i]["DomesticPercentage"]), 2).ToString("N2", new CultureInfo("en-us"));
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = string.Format("{0} %", (Convert.ToDecimal(dataSource.Data.Rows[i]["Price"]) / Convert.ToDecimal(dataSource.Data.Rows[i]["BasicPrice"]) * 100).ToString("N2", new CultureInfo("en-us")));

                            // Giá tính
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[i]["Price"])).ToString();

                            // Giá trị
                            newRow1.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["Amount"]).ToString("N0", new CultureInfo("en-us")); // Convert.ToString(dataSource.Data.Rows[i]["Amount"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngày giao dịch
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToDateTime(dataSource.Data.Rows[0]["TransactionDate"]).ToString("dd/MM/yyyy");

                    // Họ và tên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]);

                    // Số ĐKNSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Ngày cấp số ĐKNSH
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToDateTime(dataSource.Data.Rows[0]["CardDate"]).ToString("dd/MM/yyyy");

                    // Số tài khoản
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);

                    // Mã chứng khoán
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // Khối lượng
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Quantity"]);

                    // Giá tham chiếu
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[0]["BasicPrice"])).ToString();

                    // Tỷ lệ tính
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = string.Format("{0} %", (Convert.ToDecimal(dataSource.Data.Rows[0]["Price"]) / Convert.ToDecimal(dataSource.Data.Rows[0]["BasicPrice"]) * 100).ToString("N2", new CultureInfo("en-us")));

                    // Giá tính
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = Math.Ceiling(Convert.ToDecimal(dataSource.Data.Rows[0]["Price"])).ToString();

                    // Giá trị
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[0]["Amount"]).ToString("N0", new CultureInfo("en-us")); // Convert.ToString(dataSource.Data.Rows[0]["Amount"]);

                    #endregion
                }
                catch(Exception ex)
                { DisplayMessage.ShowError(ex.Message); }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_GiayDangKyMuaCK(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngày giao dịch
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // Họ và tên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // Số ĐKNSH
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Ngày cấp số ĐKNSH
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Số tài khoản
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Khối lượng
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Giá tham chiếu
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    // Tỷ lệ tính
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";

                    // Giá tính
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = "";

                    // Giá trị
                    templateRow1.ChildNodes[11].ChildNodes[0].InnerXml = "";
                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion

        /// <summary>
        /// 20081126 Haids:
        /// Báo cáo hoạt động lưu ký chứng khoán
        /// </summary>
        #region "Báo cáo hoạt động lưu ký chứng khoán"

        public static void Generate_BaoCaoHoatDongLuuKyChungKhoan_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            string boardTypeText = string.Empty;
            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_en#", string.Format("{0}", AppSettings.Report_CompanyName_en));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress#", string.Format("Trụ sở chính: {0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", string.Format("Tel: {0}", AppSettings.Report_CompanyTel));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TongTKLKmo#", Convert.ToDecimal(dataSource.TongTKLKmo).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TKLKToChuc#", Convert.ToDecimal(dataSource.TKLKToChuc).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TKLKCaNhan#", Convert.ToDecimal(dataSource.TKLKCaNhan).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SLHDCamCo#", Convert.ToDecimal(dataSource.SLHDCamCo).ToString("N0"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TransDate#", Convert.ToDateTime(dataSource.TransactionDate).ToString("dd/MM/yyyy"));
            if (dataSource.BoardType.Contains("<"))
            {
                boardTypeText = dataSource.BoardType.Replace('<', '(');
            }
            else
            {
                boardTypeText = dataSource.BoardType.ToString();
            }

            tableObject.InnerXml = tableObject.InnerXml.Replace("#BoardType#", boardTypeText);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", dataSource.BranchCodeText.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject, "STT", 2);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Mã chứng khoán
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);

                            // Số lượng CKLK của thành viên
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SoLuongCKLKThanhVien"]);

                            // Số lượng CKLK của khách hàng (Trong nước)
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SoLuongCKLKTrongNuoc"]);

                            // Số lượng CKLK của khách hàng (Nước ngoài)
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SoLuongCKLKNuocNgoai"]);

                            // Số lượng CK cầm cố
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["SoLuongCKCamCo"]);

                            // Giá trị CK cầm cố
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["GiaTriCKCamCo"]);

                            // Chứng khoán hạn chế (Tự doanh)
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKHCTuDoanh"]);

                            // Chứng khoán hạn chế (Trong nước)
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKHCTrongNuoc"]);

                            // Chứng khoán hạn chế (Nước ngoài)
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKHCNuocNgoai"]);

                            //Tổng số lượng
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Tong"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // Số lượng CKLK của thành viên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SoLuongCKLKThanhVien"]);

                    // Số lượng CKLK của khách hàng (Trong nước)
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SoLuongCKLKTrongNuoc"]);

                    // Số lượng CKLK của khách hàng (Nước ngoài)
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SoLuongCKLKNuocNgoai"]);

                    // Số lượng CK cầm cố
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["SoLuongCKCamCo"]);

                    // Giá trị CK cầm cố
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["GiaTriCKCamCo"]);

                    // Chứng khoán hạn chế tự doanh
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKHCTuDoanh"]);

                    // Chứng khoán hạn chế trong nước
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKHCTrongNuoc"]);

                    // Chứng khoán hạn chế nước ngoài
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKHCNuocNgoai"]);

                    // Tổng số lượng
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Tong"]);

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject, "STT", 2);
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK của thành viên
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK của khách hàng trong nước
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK của khách hàng nước ngoài
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Số lượng CK cầm cố
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Giá trị CK cầm cố
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Chứng khoán hạn chế tự doanh
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Chứng khoán hạn chế trong nước
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    // Chứng khoán hạn chế nước ngoài
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = "";

                    // Tổng số lượng
                    templateRow1.ChildNodes[10].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        /// <summary>
        /// 20090916 Haids: NEW Template
        /// Báo cáo hoạt động lưu ký chứng khoán NEW (Mẫu mới Issue 1692)
        /// </summary>
        public static void Generate_BaoCaoHoatDongLuuKyChungKhoanNew_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            string boardTypeText = string.Empty;
            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", string.Format("{0}", AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DepositoryCode#", AppSettings.DepositoryCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#SendTo#", AppSettings.VSDName);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", AppSettings.ReportLocation_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FromDate#", dataSource.Description);

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Mã chứng khoán
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);

                            // Số lượng CKLK Trong nước
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DomesticQuantity"]);

                            // Số lượng CKLK Nước ngoài
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["ForeignQuantity"]);

                            // Số lượng CKLK Tổng cộng
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[i]["DomesticQuantity"]) + Convert.ToDecimal(dataSource.Data.Rows[i]["ForeignQuantity"]));

                            // Tỷ lệ LK Trong nước
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(dataSource.Data.Rows[i]["DomesticPercentage"]), 2).ToString("N2", new CultureInfo("en-us"));

                            // Tỷ lệ LK Nước ngoài
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(dataSource.Data.Rows[i]["ForeignPercentage"]), 2).ToString("N2", new CultureInfo("en-us"));

                            // Tỷ lệ LK Tổng cộng
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(Convert.ToDecimal(dataSource.Data.Rows[i]["DomesticPercentage"]) + Convert.ToDecimal(dataSource.Data.Rows[i]["ForeignPercentage"])), 2).ToString("N2", new CultureInfo("en-us"));

                            // Ghi chú
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = string.Empty;

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // Số lượng CKLK Trong nước
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DomesticQuantity"]);

                    // Số lượng CKLK Nước ngoài
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["ForeignQuantity"]);

                    // Số lượng CKLK Tổng cộng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[0]["DomesticQuantity"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["ForeignQuantity"]));

                    // Tỷ lệ LK Trong nước
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(dataSource.Data.Rows[0]["DomesticPercentage"]), 2).ToString("N2", new CultureInfo("en-us"));

                    // Tỷ lệ LK Nước ngoài
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(dataSource.Data.Rows[0]["ForeignPercentage"]), 2).ToString("N2", new CultureInfo("en-us"));

                    // Tỷ lệ LK Tổng cộng
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Math.Round(Convert.ToDecimal(Convert.ToDecimal(dataSource.Data.Rows[0]["DomesticPercentage"]) + Convert.ToDecimal(dataSource.Data.Rows[0]["ForeignPercentage"])), 2).ToString("N2", new CultureInfo("en-us"));

                    // Ghi chú
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = string.Empty;

                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSSoHuuChungKhoan(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK Trong nước
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK Nước ngoài
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // Số lượng CKLK Tổng cộng
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Tỷ lệ LK Trong nước
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Tỷ lệ LK Nước ngoài
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // Tỷ lệ LK Tổng cộng
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Ghi chú
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        #region DSTongHopNguoiSoHuuCKLuuKy
        public List<XmlNode> FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling);
                                    //TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        //public static void Generate_DSTongHopNguoiSoHuuCKLuuKy_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        //{
        //    //double total = 0;

        //    #region Init
        //    XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
        //    XmlNode workSheetNode;
        //    XmlNode tableObject;
        //    #endregion

        //    #region Document Prop
        //    XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
        //    foreach (XmlNode item in DocumentPropNode)
        //    {
        //        switch (item.Name)
        //        {
        //            case "Author":
        //                {
        //                    item.InnerText = dataSource.Author;
        //                    break;
        //                }
        //            case "Description":
        //                {
        //                    item.InnerText = dataSource.Description;
        //                    break;
        //                }
        //            case "Version":
        //                {
        //                    item.InnerText = dataSource.Version;
        //                    break;
        //                }
        //            case "LastAuthor":
        //                {
        //                    item.InnerText = dataSource.LastAuthor;
        //                    break;
        //                }
        //            case "Title":
        //                {
        //                    item.InnerText = dataSource.Title;
        //                    break;
        //                }
        //            case "Subject":
        //                {
        //                    item.InnerText = dataSource.Subject;
        //                    break;
        //                }
        //            case "Company":
        //                {
        //                    item.InnerText = dataSource.Company;
        //                    break;
        //                }
        //            default:
        //                break;
        //        }

        //    }
        //    #endregion

        //    if (dataSource.Data.Rows.Count > 0)
        //    {
        //        #region Data
        //        workSheetNode = template.SelectNode(workbookNode, "Worksheet");
        //        tableObject = template.SelectNode(workSheetNode, "Table");

        //        #region replace data
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("888888888", dataSource.ParValue);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("999999999", dataSource.TotalVolume);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockAddress#", dataSource.StockAddress);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockTel#", dataSource.StockTel);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockFax#", dataSource.StockFax);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Volume#", dataSource.TotalVolume);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#TextValue#", dataSource.TextValue);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#TotalVolumeInText#", dataSource.TotalVolumeInText);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Date#", dataSource.ReportDate.ToString("dd"));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.ReportDate.ToString("MM"));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.ReportDate.ToString("yyyy"));
        //        #endregion

        //        #region Count col
        //        //int tableObjectColumn = 0;
        //        //foreach (XmlNode colNode in tableObject.ChildNodes)
        //        //{
        //        //    if (colNode.Name == "Column") { tableObjectColumn++; }
        //        //    if (colNode.Name == "Row") { break; }
        //        //}
        //        #endregion

        //        #region Find template rows
        //        //XmlNode templateRow = template.FindTemplateRow(TableObject);
        //        //XmlNode newRow = templateRow.Clone();
        //        //templateRow.ParentNode.InsertAfter(newRow, templateRow);
        //        List<XmlNode> templateRows = template.FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy(tableObject);
        //        XmlNode templateRow1 = templateRows[0];
        //        //XmlNode templateRow2 = templateRows[1];
        //        #endregion

        //        try
        //        {
        //            #region Fill data
        //            if (dataSource.Data.Rows.Count > 1)
        //                for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
        //                {
        //                    #region Bind data to Row
        //                    XmlNode newRow1 = templateRow1.Clone();

        //                    // Loại Ghi sổ
        //                    newRow1.ChildNodes[0].ChildNodes[0].InnerXml = "Ghi sổ"; // ???????

        //                    // STT
        //                    newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

        //                    // So Tên thành viên LK
        //                    //newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyNumber"]);

        //                    // Tên cổ đông
        //                    newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]);

        //                    // Địa chỉ
        //                    newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]);

        //                    // Mã số kinh doanh (CMT)
        //                    newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

        //                    // Số tài khoản
        //                    newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);

        //                    // Số lượng CK ghi sổ
        //                    newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Volume"]);

        //                    // Mã số cổ đông = Notes
        //                    newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Notes"]);
        //                    #endregion

        //                    #region Add row
        //                    tableObject.InsertAfter(newRow1, templateRow1);
        //                    #endregion
        //                }

        //            // Bind first record data

        //            #region Bind data to Row
        //            XmlNode newRow2 = templateRow1;

        //            // Loại Ghi sổ
        //            newRow2.ChildNodes[0].ChildNodes[0].InnerXml = "Ghi sổ"; // ???????

        //            // STT
        //            newRow2.ChildNodes[1].ChildNodes[0].InnerXml = "1";

        //            // So Tên thành viên LK
        //            //newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyNumber"]);

        //            // Tên cổ đông
        //            newRow2.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]);

        //            // Địa chỉ
        //            newRow2.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]);

        //            // Mã số kinh doanh (CMT)
        //            newRow2.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

        //            // Số tài khoản
        //            newRow2.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);

        //            // Số lượng CK ghi sổ
        //            newRow2.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Volume"]);

        //            // Mã số cổ đông = Notes
        //            newRow2.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Notes"]);
        //            #endregion

        //            #endregion

        //            //tableObject.InnerXml = tableObject.InnerXml.Replace("999999999", total.ToString());
        //        }
        //        catch  { }

        //        #endregion
        //    }
        //    else
        //    {
        //        #region Data
        //        workSheetNode = template.SelectNode(workbookNode, "Worksheet");
        //        tableObject = template.SelectNode(workSheetNode, "Table");

        //        #region replace data
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("888888888", dataSource.ParValue);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("999999999", dataSource.TotalVolume);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockAddress#", dataSource.StockAddress);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockTel#", dataSource.StockTel);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#StockFax#", dataSource.StockFax);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Volume#", dataSource.TotalVolume);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#TextValue#", dataSource.TextValue);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#TotalVolumeInText#", dataSource.TotalVolumeInText);
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Date#", dataSource.ReportDate.ToString("dd"));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.ReportDate.ToString("MM"));
        //        tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.ReportDate.ToString("yyyy"));
        //        #endregion

        //        #region Find template rows
        //        List<XmlNode> templateRows = template.FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy(tableObject);
        //        XmlNode templateRow1 = templateRows[0];
        //        //XmlNode templateRow2 = templateRows[1];
        //        #endregion

        //        #region Bind data to Row
        //        XmlNode newRow2 = templateRow1;

        //        // Loại Ghi sổ
        //        newRow2.ChildNodes[0].ChildNodes[0].InnerXml = " "; // ???????

        //        // STT
        //        newRow2.ChildNodes[1].ChildNodes[0].InnerXml = "1";

        //        // Tên thành viên LK
        //        //newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IdentifyNumber"]);

        //        // Tên cổ đông
        //        newRow2.ChildNodes[3].ChildNodes[0].InnerXml = "";

        //        // Địa chỉ
        //        newRow2.ChildNodes[4].ChildNodes[0].InnerXml = "";

        //        // Mã số kinh doanh (CMT)
        //        newRow2.ChildNodes[5].ChildNodes[0].InnerXml = "";

        //        // Số tài khoản
        //        newRow2.ChildNodes[6].ChildNodes[0].InnerXml = "";

        //        // Số lượng CK ghi sổ
        //        newRow2.ChildNodes[7].ChildNodes[0].InnerXml = "0";

        //        // Mã số cổ đông = Notes
        //        newRow2.ChildNodes[8].ChildNodes[0].InnerXml = "";
        //        #endregion

        //        try
        //        {
        //            tableObject.InnerXml = tableObject.InnerXml.Replace("999999999", "0");
        //        }
        //        catch { }

        //        #endregion
        //    }

        //    #region Write result            
        //    template.ExcelDocument.Save(path);
        //    #endregion
        //}

        
        #endregion

        #region DSTongHopNguoiSoHuuCKLuuKy version 2
        public List<XmlNode> FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy_Version_2(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling);
                                    //TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }
        
        public static void Generate_DSTongHopNguoiSoHuuCKLuuKy_Version_2_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data
                workSheetNode = template.SelectNode(workbookNode, "Worksheet");
                tableObject = template.SelectNode(workSheetNode, "Table");

                #region replace data
                tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
                tableObject.InnerXml = tableObject.InnerXml.Replace("8888888888", dataSource.ParValue);
                tableObject.InnerXml = tableObject.InnerXml.Replace("9999999999", dataSource.TotalVolume);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Date#", dataSource.ReportDate.ToString("dd"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.ReportDate.ToString("MM"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.ReportDate.ToString("yyyy"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyName#", AppSettings.Report_CompanyName_vi);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", AppSettings.Company_Code);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyAddress#",AppSettings.Report_CompanyAddress_vi);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyLocation#",AppSettings.Report_Location);

                #endregion

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy_Version_2(tableObject);
                XmlNode templateRow1 = templateRows[0];                
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ho ten
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // So DKNSH
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Loai hinh DK
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = "1";

                            string loaiCK = "1" ;
                            switch (Convert.ToString(dataSource.Data.Rows[i]["StockStatus"]))
                            {
                                case "P": loaiCK = "1"; break;
                                case "H": loaiCK = "2"; break;
                                default: loaiCK = "1"; break;
                            }
                            // Loai CK
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = loaiCK;

                            // So luong
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Volume"]);

                            // Ghi chu
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Notes"]);
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data

                                       
                    #region Bind data to Row
                    XmlNode newRow2 = templateRow1;

                    // STT
                    newRow2.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ho ten
                    newRow2.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // So DKNSH
                    newRow2.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Loai hinh DK
                    newRow2.ChildNodes[3].ChildNodes[0].InnerXml = "1";

                    string loaiCK0 = "1";
                    switch (Convert.ToString(dataSource.Data.Rows[0]["StockStatus"]))
                    {
                        case "P": loaiCK0 = "1"; break;
                        case "H": loaiCK0 = "2"; break;
                        default: loaiCK0 = "1"; break;
                    }
                    // Loai CK
                    newRow2.ChildNodes[4].ChildNodes[0].InnerXml = loaiCK0;

                    // So luong
                    newRow2.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Volume"]);

                    // Ghi chu
                    newRow2.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Notes"]);
                    #endregion

                   #endregion

                    //tableObject.InnerXml = tableObject.InnerXml.Replace("999999999", total.ToString());
                }
                catch { }

                #endregion
            }
            else
            {
                #region Data
                workSheetNode = template.SelectNode(workbookNode, "Worksheet");
                tableObject = template.SelectNode(workSheetNode, "Table");

                #region replace data
                tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
                tableObject.InnerXml = tableObject.InnerXml.Replace("8888888888", dataSource.ParValue);
                tableObject.InnerXml = tableObject.InnerXml.Replace("9999999999", dataSource.TotalVolume);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Date#", dataSource.ReportDate.ToString("dd"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", dataSource.ReportDate.ToString("MM"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#Year#", dataSource.ReportDate.ToString("yyyy"));
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyName#", AppSettings.Report_CompanyName_vi);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", AppSettings.Company_Code);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyAddress#", AppSettings.Report_CompanyAddress_vi);
                tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyLocation#", AppSettings.Report_Location);
                #endregion

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSTongHopNguoiSoHuuCKLuuKy_Version_2(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                #region Bind data to Row
                XmlNode newRow1 = templateRow1;

                // STT
                newRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                // Ho ten
                newRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                // So DKNSH
                newRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                // Loai hinh DK
                newRow1.ChildNodes[3].ChildNodes[0].InnerXml = "1";

                // Loai CK
                newRow1.ChildNodes[4].ChildNodes[0].InnerXml = "1";

                // So luong
                newRow1.ChildNodes[5].ChildNodes[0].InnerXml = "0";

                // Ghi chu
                newRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";
                #endregion
               

                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        #region DepositoryImportErrorList
        public List<XmlNode> FindTemplateRows_DepositoryImportErrorList(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling);
                                    //TemplateNode.Add(rowNode.NextSibling.NextSibling.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        public static void Generate_DepositoryImportErrorList_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
           // double total = 0;

            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data
                workSheetNode = template.SelectNode(workbookNode, "Worksheet");
                tableObject = template.SelectNode(workSheetNode, "Table");

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DepositoryImportErrorList(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // MA TK
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerId"]);

                            // TEN KH
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerName"]);

                            // MA CK
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);

                            // SO LUONG
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Volume"]);

                            // TRANG THAI
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockStatus"]);

                            // GIA TRI
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Value"]);

                            // GHICHU
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Notes"]);

                            try
                            {
                                // NGAYDK
                                newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["TransactionDate"]);
                            }
                            catch
                            {
                                newRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";
                            }
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data

                    #region Bind data to Row
                    XmlNode newRow0 = templateRow1;

                    // STT
                    newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // MA TK
                    newRow0.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerId"]);

                    // TEN KH
                    newRow0.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerName"]);

                    // MA CK
                    newRow0.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // SO LUONG
                    newRow0.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Volume"]);

                    // TRANG THAI
                    newRow0.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockStatus"]);

                    // GIA TRI
                    newRow0.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Value"]);

                    // GHICHU
                    newRow0.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Notes"]);

                    try
                    {
                        // NGAYDK
                        newRow0.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["TransactionDate"]);
                    }
                    catch
                    {
                        newRow0.ChildNodes[8].ChildNodes[0].InnerXml = "";
                    }
                    #endregion

                    #endregion
                    
                }
                catch { }

                #endregion
            }
            else
            {
                #region Data
                workSheetNode = template.SelectNode(workbookNode, "Worksheet");
                tableObject = template.SelectNode(workSheetNode, "Table");
                

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DepositoryImportErrorList(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                #region Bind data to Row
                XmlNode newRow0 = templateRow1;

                // STT
                newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "";

                // MA TK
                newRow0.ChildNodes[1].ChildNodes[0].InnerXml = "";

                // TEN KH
                newRow0.ChildNodes[2].ChildNodes[0].InnerXml = "";

                // MA CK
                newRow0.ChildNodes[3].ChildNodes[0].InnerXml = "";

                // SO LUONG
                newRow0.ChildNodes[4].ChildNodes[0].InnerXml = "";

                // TRANG THAI
                newRow0.ChildNodes[5].ChildNodes[0].InnerXml = "";

                // GIA TRI
                newRow0.ChildNodes[6].ChildNodes[0].InnerXml = "";

                // GHICHU
                newRow0.ChildNodes[7].ChildNodes[0].InnerXml = "";

                // NGAYDK
                newRow0.ChildNodes[8].ChildNodes[0].InnerXml = "";
                #endregion

                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        #region Right Exec DS Chot DK Mua Them
        public List<XmlNode> FindTemplateRows_DSChotDKMuaThem(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        // 20070706 TriDM: sua System.Web.HttpUtility.HtmlEncode
        // 20070713 TriDM: StockName -> StockNameViet
        // 20070922 Haids: Update thanhtien / 100.
        public static void Generate_DSChotDKMuaThem_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TransactionDate#", dataSource.TransactionDate.ToString("dd/MM/yyyy"));
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data
               

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSChotDKMuaThem(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // Ngay thuc hien
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DatePay"]);

                            // So tai khoan
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AccountId"]);

                            // Ten khach hang
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]);

                            // So CMND
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // Dia chi
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));

                            // ten cK
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["StockNameViet"]));

                            // ma CK
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);
                            
                            // KL So Huu
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Quantity"]);

                            // KL dat mua
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RegistedQuantity"]);

                            // Gia phat hanh
                            newRow1.ChildNodes[10].ChildNodes[0].InnerXml = Convert.ToInt32(dataSource.Data.Rows[i]["RightExecPrice"]).ToString();

                            // [20070922] [Haids] 
                            // anh sửa ở đây / 100 vì khi xuất danh sách chốt người đăng ký mua chứng khoán nó nhân lên 100 lần.
                            // Thanh tien
                            newRow1.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[i]["Amount"])/100) ;
                            //newRow1.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Amount"]);
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data

                    #region Bind data to Row
                    XmlNode newRow0 = templateRow1;

                    // STT
                    newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ngay thuc hien
                    newRow0.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DatePay"]);

                    // So tai khoan
                    newRow0.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["AccountId"]);

                    // Ten khach hang
                    newRow0.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]);

                    // So CMND
                    newRow0.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // Dia chi
                    newRow0.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // ten cK
                    newRow0.ChildNodes[6].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["StockNameViet"]));

                    // ma CK
                    newRow0.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // KL So Huu
                    newRow0.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Quantity"]);

                    // KL dat mua
                    newRow0.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RegistedQuantity"]);

                    // Gia phat hanh
                    newRow0.ChildNodes[10].ChildNodes[0].InnerXml = Convert.ToInt32(dataSource.Data.Rows[0]["RightExecPrice"]).ToString();

                    // [20070922] [Haids] 
                    // anh sửa ở đây / 100 vì khi xuất danh sách chốt người đăng ký mua chứng khoán nó nhân lên 100 lần.
                    // Thanh tien
                    newRow0.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToString(Convert.ToDecimal(dataSource.Data.Rows[0]["Amount"])/100);
                    //newRow0.ChildNodes[11].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Amount"]);

                    #endregion

                    #endregion

                }
                catch { }

                #endregion
            }
            else
            {
                #region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_DSChotDKMuaThem(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                #region Bind data to Row
                XmlNode newRow0 = templateRow1;

                newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[1].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[2].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[3].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[4].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[5].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[6].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[7].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[8].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[9].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[10].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[11].ChildNodes[0].InnerXml = "";
                #endregion

                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        #region DSBieuDoLKCK
        public List<XmlNode> FindTemplateRows_TongHopThongTinThucHienQuyen(XmlNode tableObjectNode)
        {
            List<XmlNode> TemplateNode = new List<XmlNode>();
            foreach (XmlNode rowNode in tableObjectNode.ChildNodes)
            {
                if (rowNode.Name == "Row")
                {
                    foreach (XmlNode cellNode in rowNode.ChildNodes)
                    {
                        if (cellNode.Name == "Cell")
                        {
                            foreach (XmlNode dataNode in cellNode.ChildNodes)
                            {
                                if ((dataNode.Name == "Data") && (dataNode.InnerText == "STT"))
                                {
                                    TemplateNode.Add(rowNode.NextSibling);
                                    return TemplateNode;
                                }
                            }
                        }
                    }
                }
            }
            return TemplateNode;
        }

        public static void Generate_TongHopThongTinThucHienQuyen_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            if (dataSource.Data.Rows.Count > 0)
            {
                #region Data


                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_TongHopThongTinThucHienQuyen(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // StockCode
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);

                            // Description
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Description"]);

                            // Rate
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Rate"]);

                            // RightExecRegisterPrice
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RightExecRegisterPrice"]);

                            // DateNoRight
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DateNoRight"]);

                            // DateOwnerConfirm
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DateOwnerConfirm"]);

                            // DatePay
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DatePay"]);

                            // RightType
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RightType"]);

                           
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data

                    #region Bind data to Row
                    XmlNode newRow0 = templateRow1;

                    // STT
                    newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // StockCode
                    newRow0.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["StockCode"]);

                    // Description
                    newRow0.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Description"]);

                    // Rate
                    newRow0.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Rate"]);

                    // RightExecRegisterPrice
                    newRow0.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RightExecRegisterPrice"]);

                    // DateNoRight
                    newRow0.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DateNoRight"]);

                    // DateOwnerConfirm
                    newRow0.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DateOwnerConfirm"]);

                    // DatePay
                    newRow0.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DatePay"]);

                    // RightType
                    newRow0.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RightType"]);

                    #endregion

                    #endregion

                }
                catch { }

                #endregion
            }
            else
            {
                #region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_TongHopThongTinThucHienQuyen(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                #region Bind data to Row
                XmlNode newRow0 = templateRow1;

                newRow0.ChildNodes[0].ChildNodes[0].InnerXml = "1";
                newRow0.ChildNodes[1].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[2].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[3].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[4].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[5].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[6].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[7].ChildNodes[0].InnerXml = "";
                newRow0.ChildNodes[8].ChildNodes[0].InnerXml = "";
                
                #endregion

                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        #region "Danh sách khách hàng mở tài khoản"
        /// <summary>
        /// 20081204 VietTQ:
        /// Báo cáo tình hình nhà đầu tư nước ngoài
        /// </summary>
        public static void GenerateForeignInvestSituationExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            SetTemplateProperties(dataSource, template, workbookNode);

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", System.Web.HttpUtility.HtmlEncode(AppSettings.Report_CompanyName_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CompanyCode#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(AppSettings.DepositoryCode)));


            tableObject.InnerXml = tableObject.InnerXml.Replace("#BranchCode#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(dataSource.BranchCodeText)));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#TradeCode#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(dataSource.Description)));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BoardType#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(dataSource.BoardType)));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Month#", string.Format("{0}/{1}", dataSource.ReportDate.Month, dataSource.ReportDate.Year));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportLocation#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(dataSource.LastAuthor)));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#FooterReport#", string.Format("{0}", System.Web.HttpUtility.HtmlEncode(dataSource.SendTo)));

            #endregion

            #region Find template rows
            List<XmlNode> templateRows = template.FindTemplateRows(tableObject, "STT", 2);
            XmlNode templateRow1 = templateRows[0];
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                try
                {
                    //Fill data
                    if (dataSource.Data.Rows.Count > 0)
                        for (int i = dataSource.Data.Rows.Count - 1; i >= 0; i--)
                        {
                            #region Bind data to Row 1

                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            //newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(i + 1);

                            // StockCode
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["StockCode"]);



                            // CustomerType
                            string LoaiKH = "CN";
                            switch (Convert.ToString(dataSource.Data.Rows[i]["CustomerType"]))
                            {
                                case "E": LoaiKH = "TC"; break;
                                default: LoaiKH = "CN"; break;
                            }
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = LoaiKH;
                            // CustomerId
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["AccountID"]);

                            // CustomerName
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // Cong dau ky
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["Quantity_DK"]).ToString("N0");

                            // % dau ky
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["Percentage_DK"]).ToString("N6");

                            // Cong cuoi ky
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["Quantity_CK"]).ToString("N0");

                            // % cuoi ky
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToDecimal(dataSource.Data.Rows[i]["Percentage_CK"]).ToString("N6");

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }
                    
                }
                catch { }

            }

            templateRow1.ParentNode.RemoveChild(templateRow1);
            int rowCount = Convert.ToInt32(tableObject.Attributes["ss:ExpandedRowCount"].Value );
            tableObject.Attributes["ss:ExpandedRowCount"].Value = ((int)(rowCount - 1 + dataSource.Data.Rows.Count)).ToString();
            #region Write result
            template.ExcelDocument.Save(path);
            #endregion

        }

        private static void SetTemplateProperties(OExcelDocumentDataSouce dataSource, OExcelDocument template, XmlNode workbookNode)
        {
            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion
        }
        #endregion

        /// <summary>
        /// 20090708 Thucnt
        /// Báo cáo thanh toán cổ tức bằng tiền mặt
        /// </summary>
        #region "Báo cáo thanh toán cổ tức bằng tiền mặt"
        public static void Generate_PaymentInMoney_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#PayDate#", dataSource.PayDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
            tableObject.InnerXml = tableObject.InnerXml.Replace("888888888888", dataSource.ParValue);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", dataSource.DateOwnerConfirm.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Purpose#", System.Web.HttpUtility.HtmlEncode(dataSource.Purpose));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockType#", dataSource.StockType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#RateA#", dataSource.RateA);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#RateB#", dataSource.RateB);

            double Rate;

            if( Convert.ToDouble(dataSource.RateB) != 0 )
            { Rate = Convert.ToDouble(dataSource.RateA) / Convert.ToDouble(dataSource.RateB) * 100; }
            else
            {Rate = 0;}

            tableObject.InnerXml = tableObject.InnerXml.Replace("#Rate#", Rate.ToString("F0"));

            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_PaymentInMoney(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Stt"]);

                            // Ten khach hang
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // So Tai khoan
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerID"]);

                            // So CMT/ Ho chieu
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // So dien thoai
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Mobile"]);

                            // Dia chi
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));
                        
                            // Quoc tich
                            //newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Country"]);

                            // So Luong CK
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Quantity"]);

                            // Co tuc
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Interest"]);

                            // Thuế thu nhập cá nhân
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["IncomeTax"]);

                            // Giá trị cổ tức sau thuế
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["InterestAfterTax"]);
                            
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1
                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerID"]);

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // So dien thoai
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Mobile"]);

                    // Dia chi
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // Quoc tich
                    //templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Country"]);

                    // So Luong CK
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Quantity"]);

                    // Co tuc
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Interest"]);

                    // Thuế thu nhập cá nhân
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["IncomeTax"]);

                    // Giá trị cổ tức sau thuế
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["InterestAfterTax"]);

                    #endregion
                }
                catch { }

                #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_PaymentInMoney(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // So dien thoai
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Quoc tich
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // So luong CK
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Co tuc
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";
                    
                    #endregion

                }
                catch { }
                    
                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        /// <summary>
        /// 20090708 Thucnt
        /// Báo cáo thanh toán cổ tức bằng cổ phiếu
        /// </summary>
        #region "Báo cáo thanh toán cổ tức bằng cổ phiếu"
        public static void Generate_PaymentInStockCode_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#PayDate#", dataSource.PayDate);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockCode#", dataSource.StockCode);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#StockName#", System.Web.HttpUtility.HtmlEncode(dataSource.StockName));
            tableObject.InnerXml = tableObject.InnerXml.Replace("888888888888", dataSource.ParValue);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#DateOwnerConfirm#", dataSource.DateOwnerConfirm.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Purpose#", System.Web.HttpUtility.HtmlEncode(dataSource.Purpose));
            //tableObject.InnerXml = tableObject.InnerXml.Replace("#StockType#", dataSource.StockType);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#RateA#", dataSource.RateA);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#RateB#", dataSource.RateB);
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_PaymentInMoney(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            // STT
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Stt"]);

                            // Ten khach hang
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["CustomerNameViet"]));

                            // So Tai khoan
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CustomerID"]);

                            // So CMT/ Ho chieu
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CardIdentity"]);

                            // So dien thoai
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Mobile"]);

                            // Dia chi
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["AddressViet"]));

                            // Quoc tich
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Country"]);

                            // So Luong CK
                            newRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Quantity"]);

                            // Co tuc
                            newRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Amount"]);

                            // Phan Le
                            newRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["Interest"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1
                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["CustomerNameViet"]));

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CustomerID"]);

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CardIdentity"]);

                    // So dien thoai
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Mobile"]);

                    // Dia chi
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["AddressViet"]));

                    // Quoc tich
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Country"]);

                    // So Luong CK
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Quantity"]);

                    // Co tuc
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Amount"]);

                    // Phan Le
                    templateRow1.ChildNodes[9].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["Interest"]);
                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_PaymentInMoney(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // STT
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";

                    // Ten khach hang
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";

                    // So tai khoan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";

                    // So CMT/ Ho chieu
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";

                    // So dien thoai
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";

                    // Dia chi
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";

                    // Quoc tich
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";

                    // So luong CK
                    templateRow1.ChildNodes[7].ChildNodes[0].InnerXml = "";

                    // Co tuc
                    templateRow1.ChildNodes[8].ChildNodes[0].InnerXml = "";

                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        /// <summary>
        /// 20090727 Thucnt
        /// Báo cáo tổng hợp tình hình hoạt động môi giới toàn công ty
        /// </summary>
        #region "Báo cáo tổng hợp tình hình hoạt động môi giới toàn công ty"
        public static void Generate_CustomerOpenClose_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path, DataTable tradingHA, DataTable tradingHO)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);

            if (tradingHA.Rows.Count > 0)
            {
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_S_Quantity#", tradingHA.Rows[0]["S_Quantity"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_S_Values#", tradingHA.Rows[0]["S_Values"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_B_Quantity#", tradingHA.Rows[0]["B_Quantity"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_B_Values#", tradingHA.Rows[0]["B_Values"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_MatchedVolume#", tradingHA.Rows[0]["MatchedVolume"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_MatchedValue#", tradingHA.Rows[0]["MatchedValue"].ToString());
            }
            else
            {
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_S_Quantity#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_S_Values#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_B_Quantity#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_B_Values#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_MatchedVolume#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HA_MatchedValue#", "0");
            }

            if (tradingHO.Rows.Count > 0)
            {
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_S_Quantity#", tradingHO.Rows[0]["S_Quantity"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_S_Values#", tradingHO.Rows[0]["S_Values"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_B_Quantity#", tradingHO.Rows[0]["B_Quantity"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_B_Values#", tradingHO.Rows[0]["B_Values"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_MatchedVolume#", tradingHO.Rows[0]["MatchedVolume"].ToString());
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_MatchedValue#", tradingHO.Rows[0]["MatchedValue"].ToString());
            }
            else
            {
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_S_Quantity#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_S_Values#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_B_Quantity#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_B_Values#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_MatchedVolume#", "0");
                tableObject.InnerXml = tableObject.InnerXml.Replace("#HO_MatchedValue#", "0");
            }
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }
                #endregion

                #region Find template rows
                //List<XmlNode> templateRows = template.FindTemplateRows(tableObject,"STT",3);
                List<XmlNode> templateRows = template.FindTemplateRows_CustomerOpenClose(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            //newRow1.ChildNodes[0].ChildNodes[0].InnerXml = "Hello";

                            // Noi dung
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[i]["Description"]));

                            // Ngay
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["DayCount"]);

                            // Luy ke tuan
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["WeekCount"]);

                            // Luy ke thang
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MonthCount"]);

                            // Luy ke nam
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["YearCount"]);

                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }
                    
                    // Bind first record data
                    #region Bind data to Row 1

                    // Noi dung
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = System.Web.HttpUtility.HtmlEncode(Convert.ToString(dataSource.Data.Rows[0]["Description"]));

                    // Ngay
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["DayCount"]);

                    // Luy ke tuan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["WeekCount"]);

                    // Luy ke thang
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MonthCount"]);

                    // Luy ke nam
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["YearCount"]);

                    #endregion
                }
                catch (Exception ex)
                {
                    DisplayMessage.ShowError(ex.Message);
                }
                #endregion
            }

            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_CustomerOpenClose(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    // Noi dung
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "";

                    // Ngay
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "0";

                    // Luy ke tuan
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "0";

                    // Luy ke thang
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "0";

                    // Luy ke nam
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "0";

                    #endregion

                }
                catch { }

                #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }
        #endregion

        /// <summary>
        /// 2009-12-22 Tuanha
        /// Phiếu đối chiếu tài khoản khách hàng
        /// </summary>
        #region "Phiếu đối chiếu tài khoản khách hàng"

        public static void Generate_CustomerCompare_ExcelDocument(OExcelDocumentDataSouce dataSource, OExcelDocument template, string path)
        {
            #region Init
            XmlNode workbookNode = template.SelectNode(template.ExcelDocument, "Workbook");
            XmlNode workSheetNode;
            XmlNode tableObject;
            #endregion

            #region Document Prop
            XmlNode DocumentPropNode = template.SelectNode(workbookNode, "DocumentProperties");
            foreach (XmlNode item in DocumentPropNode)
            {
                switch (item.Name)
                {
                    case "Author":
                        {
                            item.InnerText = dataSource.Author;
                            break;
                        }
                    case "Description":
                        {
                            item.InnerText = dataSource.Description;
                            break;
                        }
                    case "Version":
                        {
                            item.InnerText = dataSource.Version;
                            break;
                        }
                    case "LastAuthor":
                        {
                            item.InnerText = dataSource.LastAuthor;
                            break;
                        }
                    case "Title":
                        {
                            item.InnerText = dataSource.Title;
                            break;
                        }
                    case "Subject":
                        {
                            item.InnerText = dataSource.Subject;
                            break;
                        }
                    case "Company":
                        {
                            item.InnerText = dataSource.Company;
                            break;
                        }
                    default:
                        break;
                }

            }
            #endregion

            workSheetNode = template.SelectNode(workbookNode, "Worksheet");
            tableObject = template.SelectNode(workSheetNode, "Table");

            #region replace data
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyName_vi#", AppSettings.Report_CompanyName_vi);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyAddress_vi#", string.Format("{0}", AppSettings.Report_CompanyAddress_vi));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Report_CompanyTel#", AppSettings.Report_CompanyTel);
            //// Thêm dữ liệu header báo cáo
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerNameViet#", dataSource.CustomerNameViet);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerAddressViet#", dataSource.CustomerAddressViet);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#ReportDate#", dataSource.ReportDate.ToString("dd/MM/yyyy"));
            tableObject.InnerXml = tableObject.InnerXml.Replace("#CustomerID#", dataSource.CustomerID);
            tableObject.InnerXml = tableObject.InnerXml.Replace("#Balance#", dataSource.CurrentBalance.ToString());
            tableObject.InnerXml = tableObject.InnerXml.Replace("#BeforeDate#", dataSource.BeforeDate.ToString("dd/MM/yyyy"));
            #endregion

            if (dataSource.Data.Rows.Count > 0)
            {
                int tableObjectColumn = 0;

                #region Count col

                //// Đếm số cột dữ liệu trong Table
                foreach (XmlNode colNode in tableObject.ChildNodes)
                {
                    if (colNode.Name == "Column") { tableObjectColumn++; }
                    if (colNode.Name == "Row") { break; }
                }

                #endregion

                #region Find template rows

                //// Tìm cột bắt đầu fill, ví dụ báo cáo thanh toán cổ thức bằng cổ phiếu thì là cột STT
                List<XmlNode> templateRows = template.FindTemplateRows_CustomerCompare(tableObject);
                XmlNode templateRow1 = templateRows[0];

                #endregion

                try
                {
                    #region Fill data
                    if (dataSource.Data.Rows.Count > 1)
                        for (int i = dataSource.Data.Rows.Count - 1; i > 0; i--)
                        {
                            #region Bind data to Row 1
                            XmlNode newRow1 = templateRow1.Clone();

                            //// Cột số thứ tự
                            newRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["RowNumber"]);

                            //// Cột mã chứng khoán
                            newRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["MaCK"]);

                            //// Cột chứng khoán được giao dịch
                            newRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKGD"]);

                            //// Cột CK cầm cố
                            newRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKCC"]);

                            //// Cột CK chưa niêm yết
                            newRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKOTC"]);

                            //// Cột CK hạn chế
                            newRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["CKHC"]);

                            //// Cột tổng số
                            newRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[i]["TONG"]);
                            #endregion

                            #region Add row
                            tableObject.InsertAfter(newRow1, templateRow1);
                            #endregion
                        }

                    // Bind first record data
                    #region Bind data to Row 1

                    //// Cột số thứ tự
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["RowNumber"]); ;

                    // Cột mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["MaCK"]);

                    //// Cột chứng khoán được giao dịch
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKGD"]);

                    //// Cột CK cầm cố
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKCC"]);

                    //// Cột CK chưa niêm yết
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKOTC"]);

                    //// Cột CK hạn chế
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["CKHC"]);

                    //// Cột tổng số
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = Convert.ToString(dataSource.Data.Rows[0]["TONG"]);
                    #endregion
                }
                catch { }

                    #endregion
            }
            else
            {
                //#region Data

                #region Find template rows
                List<XmlNode> templateRows = template.FindTemplateRows_CustomerCompare(tableObject);
                XmlNode templateRow1 = templateRows[0];
                #endregion

                try
                {
                    #region Fill data

                    #region Bind data to Row 1

                    //// Cột số thứ tự
                    templateRow1.ChildNodes[0].ChildNodes[0].InnerXml = "1";
                    //// Cột mã chứng khoán
                    templateRow1.ChildNodes[1].ChildNodes[0].InnerXml = "";
                    //// Cột chứng khoán được giao dịch
                    templateRow1.ChildNodes[2].ChildNodes[0].InnerXml = "";
                    //// Cột CK cầm cố
                    templateRow1.ChildNodes[3].ChildNodes[0].InnerXml = "";
                    //// Cột CK chưa niêm yết
                    templateRow1.ChildNodes[4].ChildNodes[0].InnerXml = "";
                    //// Cột CK hạn chế
                    templateRow1.ChildNodes[5].ChildNodes[0].InnerXml = "";
                    //// Cột tổng số
                    templateRow1.ChildNodes[6].ChildNodes[0].InnerXml = "";
                    #endregion

                }
                catch { }

                    #endregion
            }

            #region Write result
            template.ExcelDocument.Save(path);
            #endregion
        }

        #endregion
    }
}
